BookmarkSubscribeRSS Feed
petlove
Obsidian | Level 7

Hi,

 

I am trying to create one excel file with different sheets for each dataset by using proc export. Following is my macro:

 

%macro export;


libname output "XXXXX";

 

%let domains=AE#DM;


%do i = 1 %to %eval(%sysfunc(count(&domains,#))+1);
%let dsn=%scan(&domains,&i,#);

 

proc export data=&dsn label
dbms=xlsx
outfile="test.xlsx"
replace;
;
sheet="&dsn.out" ;
run;

 

libname output;

 

%end;

%mend;

 

options merror serror symbolgen mlogic;
%export;

 

This macro overrides the existing sheet of AE.

 

Please let me know how to not override the previous sheet or append these sheets.

 

Thank you.

4 REPLIES 4
cau83
Pyrite | Level 9

Use "options mprint symbolgen;" as well as some %put statements to check the values of those parameters and see what is happening in each iteration of hte loop. It looks to me like the sheet names it would write out would be AEout.

PeterClemmensen
Tourmaline | Level 20

You can add the SHEET= along with the REPLACE option to your PROC EXPORT step.

petlove
Obsidian | Level 7

I already have SHEET= option in macro

Ksharp
Super User

libname x xlsx 'c:\temp\want.xlsx' ;

or

libname x excel 'c:\temp\want.xlsx';

 

proc copy in=work out=x;

select ae stdm;

run;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1443 views
  • 0 likes
  • 4 in conversation