BookmarkSubscribeRSS Feed
Vigneswar
Obsidian | Level 7

Hi,

 

I have scenario where I need to export multiple datasets to  a Excelsheet into different tabs.

 

Ex: I have three datasets A,B,C and i want to export into an excel workbook "Output" under sheets x,y,z correspondingly,

 

A ---> X

B ---> Y

C ---> Z

 

 

%macro Export(ouput1=,input1=,ouput2=,input2=,ouput3=,input3=);
ods excel file="/External/Output.xlsx"
options (sheet_name = "&input1.") style=statistical;
Proc report data= &output1.
style(header)=[background=LightGray];
columns col1 col2 col3;
define col1 /display ;
define col2 /display ;
define col3 /display ;
run;

ods excel options(sheet_name = "&input2");
proc report data= output2.
style(header)=[background=LightGray];
columns col1 col2 col3;
define col1 /display ;
define col2 /display ;
define col3 /display ;
run;

ods excel options(sheet_name = "&input3");
proc report data= output3.
style(header)=[background=LightGray];
columns col1 col2 col3;
define col1 /display ;
define col2 /display ;
define col3 /display ;
run;

%mend Export;

 

%Export(ouput1=A, input1=X, ouput2=B, input2=Y, ouput=C, input3=Z);

 

Out of my 3 input datasets, lets take B is an empty dataset but I wanted that corresponding sheet Y to be created as a blank one in my output excel file. Because the dataset B can be empty or have values based on that particular run on a given day.

 

Suggestions are highly appreciated!!

 

Thanks in advance,

Vigneswar

2 REPLIES 2
andreas_lds
Jade | Level 19

Why have you written a macro? Seems to be not necessary to solve the problem. I added te option sheet_interval and i got an excel file with two sheets as expected.

 

ods excel file="output.xlsx" 
      options(sheet_interval="proc");

   ods excel options(sheet_name="Class");
   proc print data=sashelp.class;
   run;

   ods excel options(sheet_name="Cars");
   proc print data=sashelp.cars;
   run;

ods excel close;

 

Sorry, i should have read the whole post 😉

The code, of course, does not solve your problem ...

andreas_lds
Jade | Level 19

Sorry, again, i have not found a way to add an empty sheet.

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
  • 2 replies
  • 966 views
  • 0 likes
  • 2 in conversation