I suggest to use the pattern suggested by @ChrisHemedinger in his blog entry Implement BY processing for your entire SAS program. Using this technique, you can run one Proc Report step for each combination followed by a Proc REPORT step for the overall table.
Take a look at the code sample here that illustrates this technique. To avoid to rerun the overall report, it is only run once at the beginning in to a document store and then it is just replayed each time. Have look and try it out.
Please note, I developed this code in Enterprise Guide, so there are no ODS statements for handling the ods destinations, except for the document destination
/*
create some sorted sample
*/
proc sort data=sashelp.cars out=cars_s;
by origin type;
run;
/*
create overall report and store it in document store
so that this only needs to run once
*/
ods document name=work.docs(write);
title "OverAll";
proc report data=cars_s;
column DriveTrain n EngineSize invoice;
define driveTrain / group;
define engineSize / analysis mean format=comma12.2;
define invoice / analysis mean format=comma12.2;
rbreak after / summarize;
run;
title;
ods document close;
/*proc document name=work.docs;*/
/* list / levels=all;*/
/*run;*/
/*quit;*/
/*
take the technique als discussed in this blog entry
http://blogs.sas.com/content/sasdummy/2012/03/20/sas-program-by-processing/
and
http://blogs.sas.com/content/sasdummy/2012/03/23/improving-on-a-sas-programming-pattern/
*/
%macro split;
%local nValues i origin type;
proc sql noprint;
select distinct
catx("!", origin, type) as origin_type
into
:origin_type1 -
from
cars_s
;
%let nValues = &sqlobs;
quit;
options nobyline;
%do i = 1 %to &nValues;
%let origin = %scan(&&origin_type&i, 1, !);
%let type = %scan(&&origin_type&i, 2, !);
%put NOTE: &sysmacroname Processing &i of &nValues &=origin &=type;
title "Report for #BYLINE";
proc report data=cars_s;
where
origin = "&origin"
and type = "&type"
;
by origin type;
column DriveTrain n EngineSize invoice;
define driveTrain / group;
define engineSize / analysis mean format=comma12.2;
define invoice / analysis mean format=comma12.2;
rbreak after / summarize;
run;
title;
/*
replay the report stored earlier
*/
proc document name=work.docs;
replay \;
run;
quit;
%end;
options byline;
%mend;
%split
Bruno
I suggest to use the pattern suggested by @ChrisHemedinger in his blog entry Implement BY processing for your entire SAS program. Using this technique, you can run one Proc Report step for each combination followed by a Proc REPORT step for the overall table.
Take a look at the code sample here that illustrates this technique. To avoid to rerun the overall report, it is only run once at the beginning in to a document store and then it is just replayed each time. Have look and try it out.
Please note, I developed this code in Enterprise Guide, so there are no ODS statements for handling the ods destinations, except for the document destination
/*
create some sorted sample
*/
proc sort data=sashelp.cars out=cars_s;
by origin type;
run;
/*
create overall report and store it in document store
so that this only needs to run once
*/
ods document name=work.docs(write);
title "OverAll";
proc report data=cars_s;
column DriveTrain n EngineSize invoice;
define driveTrain / group;
define engineSize / analysis mean format=comma12.2;
define invoice / analysis mean format=comma12.2;
rbreak after / summarize;
run;
title;
ods document close;
/*proc document name=work.docs;*/
/* list / levels=all;*/
/*run;*/
/*quit;*/
/*
take the technique als discussed in this blog entry
http://blogs.sas.com/content/sasdummy/2012/03/20/sas-program-by-processing/
and
http://blogs.sas.com/content/sasdummy/2012/03/23/improving-on-a-sas-programming-pattern/
*/
%macro split;
%local nValues i origin type;
proc sql noprint;
select distinct
catx("!", origin, type) as origin_type
into
:origin_type1 -
from
cars_s
;
%let nValues = &sqlobs;
quit;
options nobyline;
%do i = 1 %to &nValues;
%let origin = %scan(&&origin_type&i, 1, !);
%let type = %scan(&&origin_type&i, 2, !);
%put NOTE: &sysmacroname Processing &i of &nValues &=origin &=type;
title "Report for #BYLINE";
proc report data=cars_s;
where
origin = "&origin"
and type = "&type"
;
by origin type;
column DriveTrain n EngineSize invoice;
define driveTrain / group;
define engineSize / analysis mean format=comma12.2;
define invoice / analysis mean format=comma12.2;
rbreak after / summarize;
run;
title;
/*
replay the report stored earlier
*/
proc document name=work.docs;
replay \;
run;
quit;
%end;
options byline;
%mend;
%split
Bruno
If this is a stored process I do not see that it is likely to be able to insert anything into the middle of the output, which seems to be what you are asking. You would need to rewrite the process to incorporate new behavior.
Does my sample code work? If something does not work, it is always helpfull to post the log.
Hi Bruno,
I am getting a error message that another ":" is missing from the following proc sql procedure:
proc sql noprint;
select distinct
catx("!", origin, type) as origin_type
into :origin_type1 -
from cars_ ;
I am using SAS 9.2 and I don't think the above procedure, in particular, the ":origin_type1 -" is valid when using SAS 9.2.
Hi:
Please try this sample code. It should work in SAS 9.2 Your test code seems to be missing the colon after INTO, which is more to the point of why your code might not have worked.
Here is the sample code that you can test "stand-alone":
proc sort data=sashelp.cars out=cars_s;
by origin type;
run;
proc sql noprint;
select distinct
catx("!", origin, type) as origin_type
into :origin_type1 -
from cars_s
;
%let nValues = &sqlobs;
quit;
%put _global_;
and as you can see, here are the results from just this test -- no error message:
I only have SAS 9.4 to test with, but I believe that if you test this with SAS 9.2, then it should work for you.
cynthia
Here's a screen shot of the alternate syntax. cynthia
Hi Cynthia....Thanks for your suggestion and help. I will try them and see if that works. I am not too sure whether SAS 9.2 is asking for a "ending" macro variable or a second macro variable as I am selecting 2 variables. But I try both and see if one of them work and will let you know. Thanks once again.
Hi Cynthia/Bruno,
I did try your suggestions and it turned out that it did need a "ending Macro Variable" in the INTO :origin_type1 - statement. What its doing is inserting the table that is stored in the stored document on every second page and I would like it attached below the first table. I will try inserting the option startpage and see if I can get it to work....
HiCynthia/Bruno,
I was able to get it to work......just needed to insert a ODS PDF STARTPAGE=NO; just before the PROC DOCUMENT before it replayed the stored document. Thanks once again for all your help....
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.