Help using Base SAS procedures

Reg:Excel

Reply
N/A
Posts: 0

Reg:Excel

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
N/A
Posts: 0

Re: Reg:Excel

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Reg:Excel

Posted in reply to deleted_user
THe name of the excel book was Data_final and the sheet are named as sheet1,sheet2,---sheet32 .
N/A
Posts: 0

Re: Reg:Excel

Posted in reply to deleted_user
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;
N/A
Posts: 0

Re: Reg:Excel

Posted in reply to deleted_user
Thqs sukanya it worked
Valued Guide
Posts: 2,177

Re: Reg:Excel

Posted in reply to deleted_user
just use excel libname engine, like[pre] libname xl 'your excel.xls' ;
proc contents data= xl._all_ ;
run ; [/pre]
Ask a Question
Discussion stats
  • 5 replies
  • 163 views
  • 0 likes
  • 2 in conversation