BookmarkSubscribeRSS Feed
mlogan
Lapis Lazuli | Level 10

Hi All,

I have one report which contain PROC SGPLOT and PROC TABULATE for my whole dataset. I want separate report (separate PDF file) for each by group (which is 'zip' in my database). I would appreciated if anyone can help me with this.

 

part of the code was taken from: https://communities.sas.com/t5/SAS-Procedures/ODS-Need-bar-chart-and-proc-report-results-on-same-pag...

 

DATA monthly_sales; 
LENGTH zip $ 5 cty $ 8 Type $ 10; 
INPUT zip $ cty $ Type $ sales Date; 
INFORMAT Date Date9.;
FORMAT Date Date9.;
LABEL zip="Zip Code" 
cty="County" 
sales="Monthly Sales";
DATALINES; 
52423 Scott Merlot 186. 05APR2018
52423 Scott Chardonnay 156.61 11AUG2017
52423 Scott Zinfandel 35.5 11Feb2017
52423 Scott Merlot 55.3 03JAN2018
52388 Scott Merlot 122.89 01APR2016
52388 Scott Chardonnay 78.22 05Jan2018
52388 Scott Zinfandel 15.4 12MAY2018
52200 Adams Merlot 385.51 09DEC2015
52200 Adams Chardonnay 246 15JUL2016 
52200 Adams Zinfandel 151.1 19SEP2017
52200 Adams Chardonnay 76.24 25MAR2018 
52199 Adams Merlot 233.03 10OCT2016
52199 Adams Chardonnay 185.22 12Jan2017
52199 Adams Zinfandel 95.84 09Jan2018
;

PROC PRINT DATA=monthly_sales;
TITLE "Monthly Sales";
RUN;



ODS LISTING CLOSE;
OPTIONS NODATE NONUMBER;
ODS GRAPHICS / RESET=ALL;

ODS PDF FILE='C:\TEMP\TWOTHINGS.PDF' STARTPAGE=NO STYLE=EGDEFAULT;
ODS RTF FILE='C:\TEMP\TWOTHINGS.RTF' STARTPAGE=NO STYLE=EGDEFAULT;
ODS HTML PATH='C:\TEMP' (URL=NONE)

FILE="TWOTHINGS.HTML";

OPTIONS(PAGEBREAK='NO') STYLE=EGDEFAULT;

PROC SGPLOT DATA=Monthly_sales;
VBAR cty/GROUP=type GROUPDISPLAY=CLUSTER;
XAXIS TYPE=DISCRETE;
RUN;

TITLE 'PROC TABULATE';

PROC TABULATE DATA=Monthly_sales;
CLASS cty type;
VAR sales;
TABLE cty ALL,
type='SALES STATISTICS BY TYPE'*sales=' '*(N MEAN);
RUN;
TITLE;
ODS _ALL_ CLOSE;

 

5 REPLIES 5
Reeza
Super User

Unfortunately this does mean a macro solution. So you have it working for your base case, add a filter with the WHERE for each group and then wrap it in a %macro/%mend loop. Then use CALL EXECUTE to call it for each record.

I have an example here:
https://gist.github.com/statgeek/beb97b1c6d4517dde3b2

 

The good thing about doing it this way is that you can control the filename. If you use the newfile option you get PDF1, PDF2, etc. 

Cynthia_sas
SAS Super FREQ
...or use ODS DOCUMENT, but I think the Macro solution suggested by @Reeza is easier to start with.

Cynthia
mlogan
Lapis Lazuli | Level 10
Hi Cynthia, Would you be able to give me an example how to create multiple pdf files based on by group with ODS DOCUMENT. You can use my sample data if you like. Many thanks.
Cynthia_sas
SAS Super FREQ

Hi,
Take a look at this paper -- it shows PROC TABULATE with BY group processing and PROC UNIVARIATE with BY group processing. The goal in this case was to organize all the BY group output together in the new output (instead of by procedure). Look at https://support.sas.com/resources/papers/sgf09/318-2009.pdf on page 7 at Figure 11 to see the default way the output from two procedures is organized with BY group processing, compared to page 11, Figure 21 to see the rearranged output. Where you have SGPLOT and TABULATE in your example, I have TABULATE and UNIVARIATE. But the underlying concept is the same. I still think the macro approach is simpler.

 

  I haven't looked at the link @Reeza gave you, but I suspect it is something similar to what I have below:


%macro getzip;
OPTIONS NODATE NONUMBER;
ODS GRAPHICS / RESET=ALL;

** get list of unique zip codes;
proc sql noprint;
  select distinct(zip) into :wantzip separated by '~'
  from monthly_sales
  order by zip;
quit;

%put &=wantzip;

**count number of separators and calculate number of unique zips;
%let cntsep = %sysfunc(count(&wantzip,~));
%let cntzip = %eval(&cntsep+1);

** start do loop to cycle thru list of zips;
** and create the zip code macro var for each loop;
%do i = 1 %to &cntzip;
   %let zip = %scan(&wantzip,&i,~);
   %put This is for by group where zip = &zip;
    
** use zip code macro variable for file name and WHERE and TITLE;
ODS PDF FILE="C:\TEMP\TWOTHINGS_&zip..PDF" STARTPAGE=NO STYLE=EGDEFAULT nogtitle;
ODS RTF FILE="C:\TEMP\TWOTHINGS_&zip..RTF" STARTPAGE=NO STYLE=EGDEFAULT nogtitle;
ODS HTML PATH="C:\TEMP" (URL=NONE) FILE="TWOTHINGS_&zip..HTML" nogtitle
    OPTIONS(PAGEBREAK='NO') STYLE=EGDEFAULT;

title "Monthly Sales for Zip Code: &zip";
PROC SGPLOT DATA=Monthly_sales;
where zip = "&zip";
VBAR cty/GROUP=type GROUPDISPLAY=CLUSTER;
XAXIS TYPE=DISCRETE;
RUN;

TITLE 'PROC TABULATE';

PROC TABULATE DATA=Monthly_sales;
where zip = "&zip";
CLASS cty type;
VAR sales;
TABLE cty ALL,
type='SALES STATISTICS BY TYPE'*sales=' '*(N MEAN);
RUN;
TITLE;
ODS _ALL_ CLOSE;
%end;
%mend getzip;


** invoke macro program;
%getzip;

First, I make a package of program statements, called a macro program definition. The name of my macro program is %GETZIP. When the macro program is executed, the first thing it will do is make a macro variable called &WANTZIP from the monthly_sales data file. For your sample data, &WANTZIP will look like this:

WANTZIP=52199~52200~52388~52423

 

  Then the %DO loop will cycle through the list and extract 1 zip at a time into the macro variable &zip -- that macro variable is used primarily in the FILE= option, the TITLE and the WHERE statements. The ODS statements are INSIDE the macro program and so, using &zip in the FILE= option gives you a separate file for every unique zip code.

 

  In my opinion, this approach or a macro program with a CALL EXECUTE in a DATA step is an easier approach than using ODS DOCUMENT, since you already had a working SAS program to start from.

 

Cynthia


Cynthia

mlogan
Lapis Lazuli | Level 10
Thanks for the reference Reeza. I looked at the macro, but don't really know where I can fit ODS in there. Would you be able to write that on my sample code? My sample code is working.

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