The SAS Output Delivery System and reporting techniques

PROC Report

Accepted Solution Solved
Reply
Regular Contributor
Posts: 228
Accepted Solution

PROC Report

[ Edited ]
Hi...I am trying to insert a table from a second proc report below a table that was produced from the proc report. What I have been able to get so far is tables on separate pages from the first proc report and the last page has the table from the second proc report. This last table are the overall results and I would like that table to included on each page so I can compare the individual customers to the overall results. I have attached below the code I have so far....any help or suggestions would be greatly appreciated...thanks in advance.

Accepted Solutions
Solution
‎09-29-2015 01:20 PM
SAS Super FREQ
Posts: 706

Re: PROC Report

[ Edited ]

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

 

View solution in original post


All Replies
Solution
‎09-29-2015 01:20 PM
SAS Super FREQ
Posts: 706

Re: PROC Report

[ Edited ]

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

 

Regular Contributor
Posts: 228

Re: PROC Report

Hi Bruno.....thanks for your suggestion. I did try your suggestion and the output remains the same as before.....it doesn't seem to replay the strored report.
Super User
Posts: 11,134

Re: PROC Report

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.

SAS Super FREQ
Posts: 706

Re: PROC Report

[ Edited ]

Does my sample code work? If something does not work, it is always helpfull to post the log.

Regular Contributor
Posts: 228

Re: PROC Report

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 Smiley Surprisedrigin_type1 -

   from cars_ ;

 

I am using SAS 9.2 and I don't think the above procedure, in particular, the "Smiley Surprisedrigin_type1 -" is valid when using SAS 9.2.

SAS Super FREQ
Posts: 8,820

Re: PROC Report

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 Smiley Surprisedrigin_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:

sql_into_test.png

 

  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

SAS Super FREQ
Posts: 8,820

Re: PROC Report

clearly the : origin_type1 - is getting turned into an emoticon, grrrr. But in the picture of the screen shot, you can see the usage. If 9.2 wants to have an "ending" macro variable, you can always code:
into : origin_type1 - : origin_type15
SAS Super FREQ
Posts: 8,820

Re: PROC Report

Here's a screen shot of the alternate syntax. cynthia

 

sql_alt.png

Regular Contributor
Posts: 228

Re: PROC Report

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.

Regular Contributor
Posts: 228

Re: PROC Report

Hi Cynthia/Bruno,

 

I did try your suggestions and it turned out that it did need a "ending Macro Variable" in the INTO Smiley Surprisedrigin_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....

 

Regular Contributor
Posts: 228

Re: PROC Report

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....

 

🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 1035 views
  • 3 likes
  • 4 in conversation