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

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

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

Browse our catalog!

Discussion stats
  • 2 replies
  • 944 views
  • 0 likes
  • 2 in conversation