Hi,
I found many links in sas support communities under this topic, but i was not able to fix the issue.
My data consist of 134 sheets in single excel workbook with xlsx format. Each file is consisting of 3000 records.
My Question is: How to import all sheet as data set and then append them all at once?
Thanks
Ganesh
libname x xlsx '/folders/myfolders/x.xlsx';
data x;
set sashelp.vmember(keep=libname memname where=(libname='X')) end=last;
if _n_ eq 1 then call execute('proc sql;create table want as ');
call execute(cats('select * from x.',nliteral(memname) ));
if not last then call execute('union');
else call execute(';quit;');
run;
Notes (9)
53
54 libname x xlsx '/folders/myfolders/x.xlsx';
NOTE: Libref X was successfully assigned as follows:
Engine: XLSX
Physical Name: /folders/myfolders/x.xlsx
55 data x;
56 set sashelp.vmember(keep=libname memname where=(libname='X')) end=last;
57 if _n_ eq 1 then call execute('proc sql;create table want as ');
58 call execute(cats('select * from x.',nliteral(memname) ));
59 if not last then call execute('union');
60 else call execute(';quit;');
61 run;
NOTE: There were 2 observations read from the data set SASHELP.VMEMBER.
WHERE libname='X';
NOTE: The data set WORK.X has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
NOTE: CALL EXECUTE generated line.
1 + proc sql;
1 + create table want as
2 + select * from x.SHEET1
3 + union
4 + select * from x."TABLE 1 - DATA SET WORK.HAVE"N
5 + ;
NOTE: The import data set has 6 observations and 4 variables.
NOTE: The import data set has 6 observations and 4 variables.
NOTE: Table WORK.WANT created, with 6 rows and 4 columns.
5 + quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.20 seconds
cpu time 0.09 seconds
62
63 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
73
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.