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?
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;
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.
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;
Hi ballardw,
I need 10 different output files.
@ Reeza , thanks. I will do that!
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.