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

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

Spintu — Tue, 10 Jul 2018 11:57:03 GMT

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

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

lorenzo_hg — Tue, 10 Jul 2018 12:20:28 GMT

You might find the following article helpful.

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

PeterClemmensen — Tue, 10 Jul 2018 12:24:17 GMT

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?

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

Amir — Tue, 10 Jul 2018 12:26:01 GMT

Hi,

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

Regards,

Amir.

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

SuryaKiran — Tue, 10 Jul 2018 13:24:06 GMT

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

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

Spintu — Tue, 10 Jul 2018 14:38:29 GMT

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.

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

Spintu — Tue, 10 Jul 2018 14:40:01 GMT

Yes, all the datasets are from work library.

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

Spintu — Tue, 10 Jul 2018 14:42:38 GMT

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.

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

Tom — Tue, 10 Jul 2018 14:50:33 GMT

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;