Hi:
Let's start from code and data that works, that anyone can run and that only depends on variables in the data. When I run this code, I make 2 months of data for 3 regions in SASHELP.SHOES:
data newshoes;
set sashelp.shoes;
where region in ('Canada', 'Asia', 'Pacific');
** make some fake data for 2 months;
do date='01jan2019'd to '28feb2019'd by 1;
sales= sales + 1;
inventory = inventory + 1;
output;
end;
run;
Now I run these 2 PROC REPORTS to ODS PDF. I have simplified the PROC REPORTS to take all the defaults as far as style and output and the ONLY thing I have done to ODS PDF is turn STARTPAGE=NO so that I can see the summary from the 2nd PROC REPORT immediately underneath the output from the first PROC REPORT. Here's my PROC REPORT code -- I have now provided data with 3 grouping variables (REGION, PRODUCT and DATE) and 2 numeric variables SALES and INVENTORY. Anyone can run my program to make the data and run my PROC REPORT code:
ods pdf file='c:\temp\two_reports.pdf' startpage=no;
proc report data=newshoes nowd;
column region product date sales inventory ;
define region / group ;
define product/ group ;
define date / group order=internal f=monyy.;
define sales / analysis sum;
define inventory / analysis sum;
run;
proc report data=newshoes nowd;
column region product sales inventory ;
define region / group ;
define product/ group ;
define sales / analysis sum;
define inventory / analysis sum;
run;
ods pdf close;Here's the output I get:
As you can see, by default, when my first report output spills over to page 2, the headers are repeated. Then, the output from the second PROC REPORT also shows headers. The first PROC REPORT shows the summary for each month within Region and Product and the second PROC REPORT shows the summary just by REGION and PRODUCT without regard to Date.
Since you post code with variables that are not in SASHELP.PRICEDATA and since I can't run code without data, I'm taking a different approach. I don't see that you need your own "PGE" variable to control page breaking. I can get headers on the second PROC REPORT using the defaults (and without the NOHEADER option). So perhaps you can explain using my code and output as the basis, what it is about the report that my code creates that you would want to change. I did not bother to add a BREAK statement in my first PROC REPORT because I wanted to keep the output to 2 pages for both reports.
Cynthia
Hi Cynthia,
Thank you 🙂 for all the effort you put in to understand and help me in solving this problem.
I have used your code and enhanced it as per the requirement.
Two challenges which i am working on:
1. Repetitive Headers of PR1
2. PR1 header needs to be printed on next page when PR2(no header) moves to a new page.
Here is the code which i have developed, you can run this code as is and generate the PDF report
data newshoes;
set sashelp.shoes;
**removing single quotes from products;
product = compress(product, "'");
where region in ('Canada', 'Asia', 'Pacific');
** make some fake data for 2 months;
do date='01jan2019'd to '28mar2019'd by 1;
sales= sales + 1;
inventory = inventory + 1;
output;
end;
run;
**Getting distinct of Region and Product;
proc sql;
create table dist_Region_prod as
select distinct region, product from newshoes;
;quit;
**Assigning Row number;
data dist_Region_prod;
set dist_Region_prod;
sno = _n_;
run;
**creating macro variable nrows with count value;
proc sql noprint;
select count(*) as cnt into :nrows from dist_region_prod;
;quit;
ods pdf file='W:\TestProject\task\output\two_reports.pdf' startpage=no ;
%macro filter_report();
%macro d;%mend d;
%do i=1 %to &nrows;
/*Running loop for distinct region and product and assigning to a macro variable*/
proc sql noprint;
select distinct strip(region) into :region from dist_Region_prod where strip(put(sno,8.))="&i";
select distinct strip(product) into :product from dist_Region_prod where strip(put(sno,8.))="&i";
;quit;
/*filtering data for distinct region and product using macro variable created above*/
data newshoes_filtered;
set newshoes;
where region = strip("®ion.") and product = strip("&product.") ;
run;
/*PR1:*/
proc report data=newshoes_filtered nowd
style(column)=[color=black fontsize=6pt borderspacing=0 borderwidth=0 fontfamily=courier textalign=left ASIS=ON]
style(header)=[color=black fontsize=6pt background=white fontfamily=courier textalign=left borderbottomwidth=1px borderbottomstyle=solid borderbottomcolor=black borderleftwidth=0 borderrightwidth=0]
style(report)=[ASIS=ON bordercolor=white borderwidth=0 fontfamily=courier textalign=left fontsize=6pt rules=none frame=void cellspacing=0 cellpadding=4]
;
column region product date sales inventory ;
define region / group ;
define product/ group ;
define date / group order=internal f=monyy.;
define sales / analysis ;
define inventory / analysis ;
define gap / ' ' style={cellheight=.70in};
run;
/*PR2:*/
/*noheader option is used as we get headers from PR1*/
proc report data=newshoes_filtered nowd noheader
style(column)=[color=black fontsize=8pt borderspacing=0 borderwidth=0 fontfamily=courier textalign=left ASIS=ON]
style(header)=[color=black fontsize=8pt background=white fontfamily=courier textalign=left borderbottomwidth=1px borderbottomstyle=solid borderbottomcolor=black borderleftwidth=0 borderrightwidth=0]
style(report)=[ASIS=ON bordercolor=white borderwidth=0 fontfamily=courier textalign=left fontsize=7pt rules=none frame=void cellspacing=0 cellpadding=4]
;
column region product gap sales inventory ;
define region / group ;
define product/ group ;
define sales / analysis sum ;
define inventory / analysis sum;
run;
%end;
%mend filter_report;
%filter_report;
ods pdf close;
Report Output would be similar to the above image also I have highlighted the challenges.
Please ignore the formatting and indentation of PR2.
Let me know if you need anymore information.
Thank 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.
-
6091 views
-
1 like
-
-
