creating macro code for exporting different excel with multiple sheets

Vigneswar — Sat, 02 Feb 2019 07:22:17 GMT

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

Re: creating macro code for exporting different excel with multiple sheets

Cynthia_sas — Sat, 02 Feb 2019 17:08:32 GMT

Hi:
Thanks for sending your code. But without data, no one can run your code without guessing the data structure and making some fake data. And, then if they design the wrong kind of fake data, then your program will not work with the data they make up.

To help us help you, it is very useful to either illustrate your problem using SASHELP data, which everyone has access to or to provide some sample data in the form of a DATA step program that MAKES some test data for us, where the data is in YOUR structure and works with YOUR program.

Cynthia

Re: creating macro code for exporting different excel with multiple sheets

Reeza — Sat, 02 Feb 2019 18:09:40 GMT

And when posting the code, please use the code snippet as it makes it much easier to read/use the code.

topic Re: creating macro code for exporting different excel with multiple sheets in ODS and Base Reporting

creating macro code for exporting different excel with multiple sheets

Re: creating macro code for exporting different excel with multiple sheets

Re: creating macro code for exporting different excel with multiple sheets