topic Re: Reg:Excel in SAS Procedures

Reg:Excel

deleted_user — Mon, 22 Jun 2009 05:12:00 GMT

Hi

i have a Excel book in that there are 50 sheets as sheet1--sheet50 now i want to create them in to datasets.Any macros code is avaiable as i am having 20 Excelbooks like this.

Any short code that import,if any macro code is available

Re: Reg:Excel

deleted_user — Mon, 22 Jun 2009 17:56:08 GMT

I have similar code but if can list out sheet names in one list file in excel file itself, this code would be helpful:

First part of the code creat macro variable to contain sheet names. And the second part will import each sheet using that macro variable. Does this helpful ??

filename mydata DDE "Excel|C:\Documents and Settings\enugus\Desktop\[myexcel.xls]list!R2C1:R4C1" notab;

data a_null_;
infile mydata notab missover dsd dlm='09'x;
format dset $200.;
input dset$ ;
no+1;
call symput('dset'||compress(put(no,best.)),compress(dset));
call symput('no',compress(put(no,best.)));
run;

/* create each dataset*/

%macro shells(dset);
%do j=1 %to &no.;
proc import datafile="C:\Documents and Settings\enugus\Desktop\myexcel.xls" out=data&j
dbms=excel replace;
sheet="&&dset&j";
quit;
%end;
%mend shells;
%shells;

~ Sukanya E

Re: Reg:Excel

deleted_user — Tue, 23 Jun 2009 05:39:19 GMT

THe name of the excel book was Data_final and the sheet are named as sheet1,sheet2,---sheet32 .

Re: Reg:Excel

deleted_user — Tue, 23 Jun 2009 16:51:55 GMT

Okay. Try the following code. Change file location and filename. Let me know if it doesn't work.

%let dset=Sheet;
%macro shells;
%do j=1 %to 32;
proc import datafile="C:\Documents and Settings\enugus\Desktop\myexcel.xls" out=data&j
dbms=excel replace;
sheet="&dset&j";
quit;
%end;
%mend shells;

~ Sukanya E
%shells;

Re: Reg:Excel

Peter_C — Tue, 23 Jun 2009 17:00:50 GMT

just use excel libname engine, like[pre] libname xl 'your excel.xls' ;
proc contents data= xl._all_ ;
run ; [/pre]

Re: Reg:Excel

deleted_user — Wed, 24 Jun 2009 11:36:40 GMT

Thqs sukanya it worked