BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Spintu
Quartz | Level 8

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

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
Quartz | Level 8

Yes, all the datasets are from work library.

Amir
PROC Star

Hi,

 

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

 

 

Regards,

Amir.

Spintu
Quartz | Level 8

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
Meteorite | Level 14

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
Spintu
Quartz | Level 8

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
Super User Tom
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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