topic Re: Creating datasets based on 'sheets' in Excel!! in SAS Programming

Creating datasets based on 'sheets' in Excel!!

allurai0412 — Mon, 25 Mar 2013 11:10:20 GMT

hi,

I am trying to import multiple excel sheets to get different ouput datasets,....

Please check and help ...what's wrong in my approach??

%MACRO IMPR(IMP,OUT_DAT,EXL_SHT);

PROC IMPORT DATAFILE="&IMP" DBMS=EXCEL OUT=&OUT_DAT REPLACE;

SHEET="EXL_SHT";

MIXED=YES;

RUN;

%MEND IMPR;

%IMPR(C:\Users\galax_allu\Desktop\Y3DTR.XLS,PURCHASE, PUR);

%IMPR(C:\Users\galax_allu\Desktop\Y3DTR.XLS,SWITCHES, SWT);

***************Error message**************88

NOTE: Data source is connected in READ ONLY mode.

ERROR: File _IMEX_.'EXL_SHT$'n.DATA does not exist.

ERROR: Import unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.96 seconds

cpu time 0.56 seconds

LinusH — Mon, 25 Mar 2013 11:28:44 GMT

There are a bunch of SAS notes regarding this error message. I suggest that you first look thru these.

Another way to do this is to use the Libname Excel engine instead, then there is no need for looping thru the different sheets with a macro call.

allurai0412 — Mon, 25 Mar 2013 11:37:29 GMT

hi,

Thanks ...i found the error....I have not included the "&" in the line ..

SHEET="EXL_SHT";

It is working now...

Regards,

ALLU