4 weeks ago
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;
4 weeks ago - last edited 4 weeks ago
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:
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.
3 weeks ago
3 weeks ago - last edited 3 weeks ago
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:
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.
3 weeks ago