How to import an excel file with two rows of variables into SAS?

Reply
Frequent Contributor
Posts: 87

How to import an excel file with two rows of variables into SAS?

Dear All,

I'm attaching a super mini version of excel file I have which I want to export into SAS. The problem with the simple import procedure in SAS is that it needs to read the top row as variables. In this case, top two rows have same variable in each column but with a different name. For instance, A is a code for company Apple, B for company banana and so on. I can import the file if I delete either the codes or company names row. But I need both in my SAS dataset.

What's the code for that?

Thanks so much in advance!

Respected Advisor
Posts: 4,644

Re: How to import an excel file with two rows of variables into SAS?

It is not clear what you want as variable names and labels or even if you want to keep that table structure. Anyhow, this would be one way :

libname xl Excel "&sasforum.\datasets\example file.xls" HEADER=NO;


proc sql;
create table Codes as select * from xl.'Sheet1$A1:F2'n;
create table Values as select * from xl.'Sheet1$A3:F23'n;
quit;


libname xl clear;

proc transpose data=codes out=names; var F:; run;


proc sql;
select catx("=",_NAME_,COL2), cats(COL2,"='",propcase(COL2),"(",COL1,")'")
into :rename separated by " ", :label separated by " "
from names where COL2 is not missing;
quit;

data want;
set values(rename=(F1=Date &rename.));
label Date="Date" &label.;
run;

PG

PG
Super User
Super User
Posts: 6,499

Re: How to import an excel file with two rows of variables into SAS?

Do you want your variables to be named A or Apple?  What do you want to do with the other name?  Do you want it to be a label?

So if you want your variables to be named A, B, ... and to have the labels 'Apple', 'Banana', ...  Then you could do something like this.

PROC IMPORT OUT= WORK.TEST1

            DATAFILE= "C:\downloads\Example file.xls"

            DBMS=EXCEL REPLACE;

     RANGE="Sheet1$";

     GETNAMES=YES;

     MIXED=YES;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

PROC IMPORT OUT= WORK.TEST2

            DATAFILE= "C:\downloads\Example file.xls"

            DBMS=EXCEL REPLACE;

     RANGE="Sheet1$";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

* Generate LABEL statement ;

filename code temp;

data _null_;

  set test1 (obs=1);

  file code ;

  put 'label ' (_character_) (= : $quote.) ';' ;

run;

* Generate file dataset ;

data want ;

  set test2 (firstobs=2);

  %inc code / source2;

run;

Ask a Question
Discussion stats
  • 2 replies
  • 314 views
  • 0 likes
  • 3 in conversation