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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
ballardw
Super User

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.

Reeza
Super User

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;

metallon
Pyrite | Level 9

Hi ballardw,

I need 10 different output files.

@ Reeza , thanks. I will do that!

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!

How to Concatenate Values

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.

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
  • 3 replies
  • 1086 views
  • 3 likes
  • 3 in conversation