Help using Base SAS procedures

How to create a single file with multiple tabs from tables created by a macro called # times

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How to create a single file with multiple tabs from tables created by a macro called # times

Hi all,

 

I am new to SAS and I am trying to work on macros. I want to know if I can create a single excel file with multiple tabs using tables created by a macro which is calle dn number of times. 

 

/*start of macro */

%macro exampeMacro(vendor, environment)

proc sql;

create table work.&vendor._&environment_enrl

(

select distinct abc,

def,

ghi

from database.tableName

where xyz = something

);

quit;

 

proc export data = work.&vendor._&environment_enrl

outfile = "location/nameOfFile.txt

replace label dbms= dlm ; delimiter= '|';
run;

 

%mend exampeMacro;

/* end of macro */

 

%exampeMacro(vendor1, prod);

%exampeMacro(vendor1, test);

 

%exampeMacro(vendor2, prod);

%exampeMacro(vendor2, test);

 

This is just an example simple code I wrote just now in 2 minutes. Now I know if I have the file creration within the macro, it will create 4 different files. I want only 2 files, one for each vendor with 2 tabs, one for prod and one for test. 

 

Macro is called multiple times and it creates multiple tables but I want to output only 1 file for each vendor. Please help me and let me know if this is possible.

 

Many Thanks,

 

mraza12.


Accepted Solutions
Solution
‎04-05-2018 04:16 PM
SAS Employee
Posts: 3

Re: How to create a single file with multiple tabs from tables created by a macro called # times

 

Hello,

 

Here is my suggestion (hopefully this is what you would want to happen). I have the sashelp.cars dataset and I want to have an excel file with tabs for each make that I choose:

%macro example(vendor,Make);

libname test xlsx 'C:\placetoputfile\CommEx.xlsx';
	proc sql;
		create table test.&vendor._&Make as
		select Make, Model,MSRP
		from sashelp.cars
		where Make="&Make";
	quit;
	
%mend example;



%example(ABC,Acura)
%example(ABC,Honda)

libname test clear;

 

 

What this does is create a workbook called CommEx and it creates a backup file as well. The backup file holds the Acuras and the Commex.xlsx will show both Acura and Honda

 

exceloutput.PNG

View solution in original post


All Replies
Super User
Posts: 23,776

Re: How to create a single file with multiple tabs from tables created by a macro called # times

Yes it's possible, but if you're new to SAS macros is not the place to start.

 

I would recommend looking at ODS EXCEL instead. You may still need macros if you need multiple sheet, but it handles the multiple sheet aspects easier. 

 

Also, before you 'generalize' or automate a procedure you should have a base case working. What does it look like when you do this once manually. Then identify the parts that need to change. The easiest way to do this is to literally copy and paste the code and identify which parts need to change. Convert those to macro variables and that's how you build your macro code. First make them macro variables, once that works, create it as a macro with parameters instead.

Super User
Posts: 23,776

Re: How to create a single file with multiple tabs from tables created by a macro called # times

[ Edited ]

Also, you're not using the SHEET option on PROC EXPORT to specify what sheet you want.

 

EDIT: NVM, realized your example is a text export even though you're talking about Excel.

New Contributor
Posts: 4

Re: How to create a single file with multiple tabs from tables created by a macro called # times

Yes, I know it will not be sheet and not a text file. This is what I was doing previously. I know how to work with macro variables. It is a little challenging for me because I would want to create the file outside of the macro which means I should make those variables global. I'd appreciate a sample code if anyone would like to pitch in.

Super User
Posts: 23,776

Re: How to create a single file with multiple tabs from tables created by a macro called # times


@mraza12 wrote:

 I would want to create the file outside of the macro which means I should make those variables global. I'd appreciate a sample code if anyone would like to pitch in.


 

Here's a fully automated version.

 

This example creates a workbook for each Origin in the cars data set and each workbook has an individual named sheet for the Makes. It uses ODS EXCEL and BY group processing so you don't need to know the number of makes or origins and is fully data driven.

 

 


%*Generate sample data to work with here;
proc sort data=sashelp.cars out=cars;
by origin make;
run;

*Close other destinations to improve speed;
ods listing close;
ods html close;

*macro that exports to file with Origin in file name and a 
sheet for each make. The number of origins or makes is not 
needed ahead of time;

%macro export_origin(origin=);

%*filename for export, set style for fun and add label for each sheet;
ods excel file="C:\_localdata\Cars_&origin..xlsx" 
    style = meadow 
    options(sheet_interval='bygroup' 
            sheet_label='Make');

*generate a sheet for each make (by make);
proc print data=cars noobs label;
where origin = "&Origin";
by make;
run;

%*close excel file;
ods excel close;

%mend;

*calls macro for each origin in file. 
number of origins doesn't need to be known ahead of time;

data _null_;
    set cars;
    by origin;

    if first.origin then do;
        *create macro call;
        str = catt('%export_origin(origin=', origin, ');');
        *call macro;
        call execute(str);

    end;

run;

%*reopens output destinations;
ods html;
ods listing;

 

https://github.com/statgeek/SAS-Tutorials/blob/master/SAS_export_multiple_workbook_worksheet.sas

Super User
Posts: 13,583

Re: How to create a single file with multiple tabs from tables created by a macro called # times

With that specific example you have an issue because the Proc Export has 1) REPLACE and 2) only references one output file with a fixed name. So each run of the macro would REPLACE the existing output data set.

 

In a more general sense of your example you might consider BY Group processing or a report procedure. Since your example shows the one fixed data set as the source and you want a tab for each vendor and environment combination that lends itself to by groups. With ODS Excel to set the output options you can have a separate tab of output for each combination.

 

For example the following code produces one tab for each level of sex with summary statistics requested for each age as a row.

(change the d: to an appropriate path for your system. Requires SAS/Access Interface to PC Files to run as shown)

ods excel file="d:\junk.xlsx";
proc tabulate data=sashelp.class;
   class sex;
   class age;
   var height weight;
   table sex,
         age,
         (height weight)*(max min)
         ;
run;

ods excel close;

 

Other things would be needed depending on exact data and desired result but note that for some moderately obnoxious output (this could generate a page for each NAME in the data set for example. And since Proc Tabulate allows multiple table statements within a single Proc tabulate call you can generate a LOT of tabs from a single data set. With nary a macro in sight.

Solution
‎04-05-2018 04:16 PM
SAS Employee
Posts: 3

Re: How to create a single file with multiple tabs from tables created by a macro called # times

 

Hello,

 

Here is my suggestion (hopefully this is what you would want to happen). I have the sashelp.cars dataset and I want to have an excel file with tabs for each make that I choose:

%macro example(vendor,Make);

libname test xlsx 'C:\placetoputfile\CommEx.xlsx';
	proc sql;
		create table test.&vendor._&Make as
		select Make, Model,MSRP
		from sashelp.cars
		where Make="&Make";
	quit;
	
%mend example;



%example(ABC,Acura)
%example(ABC,Honda)

libname test clear;

 

 

What this does is create a workbook called CommEx and it creates a backup file as well. The backup file holds the Acuras and the Commex.xlsx will show both Acura and Honda

 

exceloutput.PNG

New Contributor
Posts: 4

Re: How to create a single file with multiple tabs from tables created by a macro called # times

Thanks a lot brzcol! That works for me Smiley Happy I really appreciate your help and example.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 252 views
  • 0 likes
  • 4 in conversation