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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
brzcol
SAS Employee

 

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

7 REPLIES 7
Reeza
Super User

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.

Reeza
Super User

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.

mraza12
Calcite | Level 5

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.

Reeza
Super User

@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

ballardw
Super User

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.

brzcol
SAS Employee

 

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

mraza12
Calcite | Level 5
Thanks a lot brzcol! That works for me 🙂 I really appreciate your help and example.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 6171 views
  • 1 like
  • 4 in conversation