Import Excel Cross Tab table into SAS

Is there a way to import an Excel Cross tab table into SAS without having to reformat it to the SAS format first
                                  Race
Excel                        White   Hispanic   Asian
Age Cat  00-01y    524       845        123
               01-04y    1000     2501       456
               05-14y     895      2512       561

Procedure to manually enter into SAS-I want to avoid this        
input Age $ Race $  Pop $;
datalines;
00-01y  White       524  
00-01y  Hispanic   845
00-01y  Asian       123
....etc.
SASEPIAsked:
Who is Participating?
 
d507201Database Marketing ConsultantCommented:
If you have SAS/Access for PC File Formats then with the excel libname engine you can read excel files as if they were SAS data sets.

libname xls excel '/path_to_spreadsheet/filename.xls';

data one; set xls.'sheetname$'n;
run;

If you have only Base SAS then use proc import.

http://support.sas.com/onlinedoc/913/docMainpage.jsp
0
 
SASEPIAuthor Commented:
Thanks d507201,

I was able to import it, however, I actually want to merge this dataset with another dataset by age category and race.  I need to do this so I can use the population variable in a calculation. Therefore, I need to assign a race  to each value, (as below) right? or is there a way to do this from the cross tab table;or is there an easy way to convert the cross tab table to the format below.  Thank you.
age cat   race        population
00-01y  White       524  
00-01y  Hispanic   845
00-01y  Asian       123
0
 
d507201Database Marketing ConsultantCommented:
I'm thinking there's a way to do it using proc transpose but couldn't quickly get it to work.  Here's a sort of brute force approach.

** Create a SAS data set that looks like a 2x2 table. ;
data test; infile cards;
  input ageCat $ white hispanic asian;
cards;
01 5 4 6
04 8 3 2
14 10 32 44
;
run;

title 'Test';
proc print data=test;
run;


data new; set test;
      length race $10;
  if white > 0 then do;
  race='white'; pop=white; output;
  end;
  if hispanic > 0 then do;
  race='hispanic'; pop=hispanic; output;
  end;
  if asian > 0 then do;
  race='asian'; pop=asian; output;
  end;

  keep ageCat race pop;
run;


title 'new';
proc print data=new;
run;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.