Export multiple datasets to multiple sheets(Imp: excluding variable name/label in datasets)

Question

I have few datasets which i have to export to excel.

For example: dataset: ds1

Variable_1 Variable_2 Variable_3

datax datay dataz

dataset: ds2

Variable_A Variable_B Variable_X

dataxxx datayyy datazzz

Requirement:

Export these datasets to excel sheet with out variable names/labels. example excel sheet should look like: datax datay dataz

i.e., with out variable names/labels and only data.

I tried using proc export with dbms as csv

proc export data=ds1
dbms=csv
outfile="ds1_data.csv"
replace;
putnames=NO;
run;

proc export data=ds2
dbms=csv
outfile="ds2_data.csv"
replace;
putnames=NO;
run;

It is working fine with putnames="No" option.

However this creates multiple csv files. I need a single excel sheet with multiple sheets (with out variable names)

Any options available?

You can't use CSV to write a multisheet anything, CSV has no sheet concept. If you want an excel file, you have to use DBMS=EXCEL or XLS or XLSX, or an ODS variation. — Joe
@Joe. Thanks! I tried Tagsets.ExcelXp and it is working perfectly. — Kay

user3658367 user3658367 · Accepted Answer · 2015-07-27T14:50:48

@Kay You can write this way.

proc export data=ds1
dbms=xls
outfile="ds1_data.xls"
replace;
putnames=NO;
sheet=ds1;
run;

proc export data=ds2
dbms=xls
outfile="ds1_data.xls"
replace;
putnames=NO;
sheet=ds2;
run;

Give the sheet name and, change the dbms to xls and give the same location for the file to have more than one datasets in the same excel workbook. Sheet= option might not work when DBMS = csv if I am not wrong.

Export multiple datasets to multiple sheets(Imp: excluding variable name/label in datasets)

1 Answers