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);
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);
You might find the following article helpful.
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?
Yes, all the datasets are from work library.
Hi,
Have you considered appending the data sets before writing the appended one out, e.g. proc append?
Regards,
Amir.
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.
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);
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.