DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

 

 

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);


Accepted Solutions
Solution
Tuesday
Valued Guide
Posts: 589

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

[ Edited ]

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);

 

 

Thanks,
Suryakiran

View solution in original post


All Replies
New Contributor
Posts: 3

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

You might find the following article helpful.

PROC Star
Posts: 1,265

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

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?

Occasional Contributor
Posts: 19

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

Yes, all the datasets are from work library.

Super Contributor
Posts: 339

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

Hi,

 

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

 

 

Regards,

Amir.

Occasional Contributor
Posts: 19

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

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.

 

Solution
Tuesday
Valued Guide
Posts: 589

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

[ Edited ]

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);

 

 

Thanks,
Suryakiran
Occasional Contributor
Posts: 19

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

Posted in reply to SuryaKiran

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.

Super User
Super User
Posts: 8,070

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

[ Edited ]

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 80 views
  • 4 likes
  • 6 in conversation