Hi,
Here is the scenario,
I am trying to create a macro code that produces xml file for different businesses that will have multiple sheets.
Example,
Business : Snacks, Diary, Choco and each business has multiple sub cat that comes in the sheets of the xml file.
When I execute the macro, the output should be three different xml files for each business (Snacks.xml, Diary.xml, Choco.xml) and with the sub cat in their respective sheets.
Below is the code I created,
-----------------------------------------------------------------------------------------------------------------------------------------------
%MACRO TABLES(INPUT=, OUTPUT1= , OUTPUT2= ,OUTPUT3= ,COND1= , c1= );
%Local INPUT OUTPUT1 output2 COND c1 c2;
PROC SQL NOPRINT;
CREATE TABLE &OUTPUT1. AS
SELECT * FROM &INPUT.
WHERE &c1="&COND1.";
QUIT;
proc sql noprint;
CREATE TABLE &OUTPUT2. AS
SELECT * FROM &OUTPUT1.
WHERE country in ('India');
QUIT;
proc sql noprint;
CREATE TABLE &OUTPUT3. AS
SELECT * FROM &OUTPUT1.
WHERE country in ('USA');
QUIT;
%MEND TABLES;
%macro Export(output1=, input1=, output2=, input2=, output3=, input3=);
ods tagsets.ExcelXP file="/sasconfig/Lev1/External/&file..xml" style=meadow
options (sheet_name = "&input1.");
ods results on;
proc report data= &output1.
style(header)=[background=LightGray font_weight=Bold borderwidth=1] ;
columns a b c d e;
define a /display ;
define b /display ;
define c /display;
define d /display;
define e /display;
run;
ods tagsets.Excelxp options(sheet_name = "&input2.");
proc report data= &output2.
style(header)=[background=LightGray font_weight=Bold borderwidth=1] ;
columns a b c d e;
define a /display ;
define b /display ;
define c /display;
define d /display;
define e /display;
run;
ods tagsets.Excelxp options(sheet_name = "&input3.");
proc report data= &output3.
style(header)=[background=LightGray font_weight=Bold borderwidth=1] ;
columns a b c d e;
define a /display ;
define b /display ;
define c /display;
define d /display;
define e /display;
run;
.
.
.
.
ods _all_ close;
%mend Export;
%Export(output1=Snacks_A,output2=Snacks_B, output3=Snacks_C,
input1=India, input2=USA, input3=Australia);
%Export(output1=Choco_A,output2=Choco_B, output3=Choco_C,
input1=India, input2=USA, input3=Australia);
---------------------------------------------------------------------------------------------------------------------------------
Here I am using multiple proc reports for exporting multiple sheets into single business file.
The sheets for different businesses varies and max of 6 sheets (country).
I am trying to modify the code with a single proc report. Any suggestions are highly appreciated!
Thanks in advance!!!
Vigneswar
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.