Exporting file in Excel file into a single sheet from multiple SAS datsets

[Spintu]

Hi,

Can you please someone suggest me , while doing the below steps. In out put I am getting only last datasets records.

I want pull all the table information into a single sheet.

All the tables having same column.

%macro export(dst=);
 proc export  data=&dst.
  outfile="&rptpath./XYZ.xls"
  Label dbms=xls Replace;
  sheet="sheet1";
 run;
%mend;

%export(dst=ABC);
%export(dst=ABD);
%export(dst=ASD);
%export(dst=ASF);

[lorenzo_hg]

You might find the following article helpful.

[PeterClemmensen]

You want to export multiple datasets into the same excel sheet correct?

Where do you want the datasets to be? On top of each other or?

[Spintu]

Yes, all the datasets are from work library.

[Amir]

Hi,

Have you considered appending the data sets before writing the appended one out, e.g. proc append?

Regards,

Amir.

[Spintu]

yes I did proc append . it is happening correctly. I just wanted to know without proc append in proc export do you have any option so that we can append all the records in one single sheet.

[SuryaKiran · Accepted solution]

When every you call the macro %export() with a dataset then entire xls file will be replaced with that data. As @Amir mentioned put them in single dataset and then export into single file.

data ABC ABD ASD ASF;
set sashelp.class;
if _n_<5 then output ABC;
else if _n_<10 then output ABD;
else if _n_<15 then output ASD;
else output ASF;
run;

Options symbolgen mprint mlogic;
%MACRO APPEND(ds=);
%DO I=2 %TO %sysfunc(countw(&ds));
%let BASE_DS=%SCAN(%QUOTE(&DS),1);
%let Data_ds=%SCAN(%QUOTE(&DS),&i);
PROC APPEND BASE=&Base_ds DATA=&Data_ds;
RUN;
%END;
proc export 
 data=ABC 
 outfile='/usr/apps/sasdata/CAO/CAOSec/local_access/CAO_DropBox/Kiran/test_append.xlsx'
 dbms=xlsx replace;
sheet='sheet1';
run;
%MEND APPEND;
%APPEND(ds=ABC ABD ASD ASF);

[Spintu]

yes I did proc append . it is happening correctly. I just wanted to know without proc append in proc export do you have any option so that we can append all the records in one single sheet.

[Tom]

You could possibly figure out how to write to a specific starting CELL in the Excel file.  But it is probably not worth it.

Note that you can use the XLSX engine to write directly to an Excel file . So you could just combine the datasets as you write them by using a simple data step that sets multiple datasets together.

libname out xlsx 'myexcel.xlsx';
data out.want ;
  set a b c d e ;
run;