The SAS Output Delivery System and reporting techniques

Looping around ods/proc report to generate xml based on variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

Looping around ods/proc report to generate xml based on variable

Hi,

I have the data set trialdata that is used to create a report like this:

ods tagsets.excelxp_mod file='C:\temp\MYID_TRIALS.xml' style=styles.mycssstylescreen

options(

        embedded_titles='yes'

        Embedded_Footnotes = 'yes'

        Orientation = 'landscape'

        FitToPage = 'yes'

        Frozen_Headers = '7'

        Autofit_Height ='yes'

        Sheet_Name ='RESULTS TRIAL'

        );

proc report data=trialdata split='*';

TITLE  JUSTIFY=L Test ;

COLUMN TRIALID TRIALID_VAR CATEG CATEG_VAR SPECIES SPECIES_VAR MATRIX NEGATIVE

         ('Masterheader'(

                     ('' MN_POS N_POS M_POS OTH_POS TOTALC))

                     );

DEFINE TRIALID / GROUP ORDER=DATA NOPRINT;

DEFINE TRIALID_VAR / COMPUTED'TRIALID';

DEFINE SPECIES / GROUP ORDER=DATA NOPRINT;

DEFINE SPECIES_VAR / COMPUTED'SPECIES';

DEFINE CATEG / GROUP ORDER=DATA NOPRINT;

DEFINE CATEG_VAR / COMPUTED'CATEG';

DEFINE MATRIX / 'Mat';

DEFINE NEGATIVE / 'Neg';

DEFINE MN_POS / 'N_M';

DEFINE N_POS / 'N';

DEFINE M_POS / 'M';

DEFINE OTH_POS / 'Others';

DEFINE TOTALC / 'TOTALC';

BREAK AFTER TRIALID / summarize style=[backgroundcolor=#C8CCA8 fontsize=1 fontfamily=arial];

BREAK AFTER SPECIES  /  summarize style=[backgroundcolor=#F4F6E9 fontsize=1 fontfamily=arial];

BREAK AFTER CATEG / summarize style=[backgroundcolor=#E5EAC1 fontsize=1 fontfamily=arial];

COMPUTE TRIALID_VAR / CHARACTER LENGTH=55;

    TRIALID_VAR = TRIALID;

IF upcase(_break_) = 'TRIALID' THEN

    TRIALID_VAR=catx(' ','TOTALC', TRIALID);

ENDCOMP;

COMPUTE CATEG_VAR / CHARACTER LENGTH=55;

    CATEG_VAR = CATEG;

IF upcase(_break_) = 'CATEG' THEN

    CATEG_VAR=catx(' ','TOTALC', CATEG);

ELSE IF upcase(_break_) = 'SPECIES' THEN DO

TRIALID_VAR='';

CATEG_VAR='';

END;

ENDCOMP;

COMPUTE SPECIES_VAR / CHARACTER LENGTH=55;

    SPECIES_VAR = SPECIES;

IF upcase(_break_) = 'SPECIES' THEN

    SPECIES_VAR=catx(' ','TOTALC', SPECIES);

ELSE IF upcase(_break_) = 'CATEG' THEN

TRIALID_VAR='';

ENDCOMP;

run;

ods tagsets.excelxp_mod;

ods _all_ close;

but I need this report 10 times for 10 different location. There is a location ID that I have not included in the report.

Is there a neat way to maybe wrap a loop around the ods tagset definition or proc report to look through 10 static location ID's

and produce one xml (excel) file for each?


Accepted Solutions
Solution
‎08-18-2015 01:11 PM
Super User
Posts: 17,898

Re: Looping around ods/proc report to generate xml based on variable

If you want one file per location then I think you'll need a macro loop. Fortunately it's not too hard to create.

1. Create macro from original code (sketched code below)

2. Generate list of locations

3. Call macro with each location

%macro export_data(location);

ods tagsets.excelxp_mod file="C:\temp\MYID_TRIALS_&location..xml"

<rest of SAS code>


%mend export_data;


proc sort data=trialdata out=loc nodupkey;

by location;

run;


data _null_;

set loc;

str='%export_data('||location||');';

call execute(str);

run;

View solution in original post


All Replies
Super User
Posts: 10,527

Re: Looping around ods/proc report to generate xml based on variable

Do you need to create 10 separate output files or have the output grouped  by that location variable?

If the last is acceptable the easiest is 1) sort the data by the location variable and 2 ) include a BY Loaction; statement in your proc report.

Solution
‎08-18-2015 01:11 PM
Super User
Posts: 17,898

Re: Looping around ods/proc report to generate xml based on variable

If you want one file per location then I think you'll need a macro loop. Fortunately it's not too hard to create.

1. Create macro from original code (sketched code below)

2. Generate list of locations

3. Call macro with each location

%macro export_data(location);

ods tagsets.excelxp_mod file="C:\temp\MYID_TRIALS_&location..xml"

<rest of SAS code>


%mend export_data;


proc sort data=trialdata out=loc nodupkey;

by location;

run;


data _null_;

set loc;

str='%export_data('||location||');';

call execute(str);

run;

Frequent Contributor
Posts: 133

Re: Looping around ods/proc report to generate xml based on variable

Hi ballardw,

I need 10 different output files.

@ Reeza , thanks. I will do that!

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 463 views
  • 3 likes
  • 3 in conversation