BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
deleted_user
Not applicable
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
deleted_user
Not applicable
THe name of the excel book was Data_final and the sheet are named as sheet1,sheet2,---sheet32 .
deleted_user
Not applicable
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;
deleted_user
Not applicable
Thqs sukanya it worked
Peter_C
Rhodochrosite | Level 12
just use excel libname engine, like[pre] libname xl 'your excel.xls' ;
proc contents data= xl._all_ ;
run ; [/pre]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 787 views
  • 0 likes
  • 2 in conversation