Help on Import data

[Ganeshk]

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

[Ksharp]

Code: Program

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;

---

Log: Program

Notes (9)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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