BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
twildone
Pyrite | Level 9
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.
1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

11 REPLIES 11
BrunoMueller
SAS Super FREQ

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

 

twildone
Pyrite | Level 9
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.
ballardw
Super User

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.

BrunoMueller
SAS Super FREQ

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

twildone
Pyrite | Level 9

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.

Cynthia_sas
SAS Super FREQ

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:

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

Cynthia_sas
SAS Super FREQ
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
Cynthia_sas
SAS Super FREQ

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

 

sql_alt.png

twildone
Pyrite | Level 9

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.

twildone
Pyrite | Level 9

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

 

twildone
Pyrite | Level 9

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

 

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
  • 11 replies
  • 2763 views
  • 3 likes
  • 4 in conversation