excel to sas

SASPhile — Tue, 02 Jun 2009 18:44:17 GMT

%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!

Re: excel to sas

Doc_Duke — Tue, 02 Jun 2009 18:46:57 GMT

The data step has to be inside the import_xls macro to "see" the value.

Re: excel to sas

deleted_user — Thu, 11 Jun 2009 20:30:27 GMT

Declare
%let dat = ;

before calling the macro.
i.e. before:
%import_xls(ABC_2009_05);

topic Re: excel to sas in SAS Programming

excel to sas

Re: excel to sas

Re: excel to sas