DATA Step, Macro, Functions and more

excel to sas

Reply
Super Contributor
Posts: 647

excel to sas

%let path=C:\inputfile;
%macro import_xls(dat);
PROC IMPORT OUT= &dat
DATAFILE= "&path.\may data.xls"
DBMS=EXCEL REPLACE;
SHEET="&dat";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;

RUN;
%mend;
%import_xls(ABC_2009_05);
%import_xls(pqr_2009_05);

I will have data from 16 vendors saved in 16 tabs under may data.xls. I will be creating sas datasets.
for each dataset I will have to create two extra fields provider and filedate.
for example:
data ABC_2009_05;
set ABC_2009_05;
provider='ABC';
filedate='2009-05';
run;

I tried this piece of code:
data &dat;
set &dat;
provider=scan(&dat,1,'_');
filedate=scan(&dat,2,'_')||'-'||scan(&dat,3,'_');
run;

But &dat is not getting resolved!
Trusted Advisor
Posts: 2,113

Re: excel to sas

The data step has to be inside the import_xls macro to "see" the value.
N/A
Posts: 0

Re: excel to sas

Declare
%let dat = ;

before calling the macro.
i.e. before:
%import_xls(ABC_2009_05);
Ask a Question
Discussion stats
  • 2 replies
  • 103 views
  • 0 likes
  • 3 in conversation