Hi.....I am trying to create a report from 2 datasets ( finaldataset & finaldataset3) where tables are created and stacked on one another where the CH_Number, CH_Name, and SHA are the same from each dataset. That is, each page in the report should have a table from each dataset where the CH_Number, CH_Name, and SHA are the same. I am able to create the report with the first table from the first PROC Report but the second table isn't created. Any suggestions.
ODS _ALL_ CLOSE;
ODS LISTING CLOSE;
ODS NORESULTS;
OPTIONS ORIENTATION=PORTRAIT NOCENTER NODATE NONUMBER
TOPMARGIN=".5IN"
BOTTOMMARGIN=".5IN"
LEFTMARGIN=".5IN"
RIGHTMARGIN=".5IN";
OPTIONS NOQUOTELENMAX;
ODS PDF FILE="%sysfunc(pathname(project))\&FILENAME Report (&rundate).PDF" NOTOC STYLE=MINIMAL BOOKMARKGEN=NO BOOKMARKLIST=NONE STARTPAGE=NO;
%MACRO SPLIT;
%LOCAL nValues i CH_Number CH_Name SHA;
PROC SQL NOPRINT;
SELECT DISTINCT
(catx("!", CH_Number, CH_Name, SHA)) as CH_Number_CH_Name_SHA
INTO :CH_Number_CH_Name_SHA1 - :CH_Number_CH_Name_SHA800
FROM FINALDATASET;
%LET nValues = &sqlobs;
QUIT;
OPTIONS NOBYLINE;
%DO i = 1 %TO &nValues;
%LET CH_Number = %SCAN(&&CH_Number_CH_Name_SHA&i, 1, !);
%LET CH_Name = %SCAN(&&CH_Number_CH_Name_SHA&i, 2, !);
%LET SHA = %SCAN(&&CH_Number_CH_Name_SHA&i, 3, !);
%PUT NOTE: &sysmacroname Processing &i of &nValues &=CH_Number &=CH_Name &=SHA;
ODS PDF FILE="%sysfunc(pathname(project))\&FILENAME Report (&rundate).PDF" STYLE=MINIMAL NOTOC BOOKMARKGEN=NO BOOKMARKLIST=NONE STARTPAGE=NOW;
OPTIONS NOBYLINE;
OPTIONS NOQUOTELENMAX;
PROC REPORT DATA=FINALDATASET NOWD MISSING ps=43 ls=108
style(report)={frame=box just=CENTER WIDTH=100%}
STYLE(header)={font_weight=bold background=lightgrey font_face=Arial font_size=8pt borderwidth=1px bordercolor=black just=CENTER}
STYLE(COLUMN)={background=white font_face=Arial font_size=7pt borderwidth=1px bordercolor=black just=CENTER}
STYLE(LINES)={JUST=C FONT_WEIGHT=BOLD FONT_SIZE=11PT};
WHERE CH_Number ="&CH_Number"
AND CH_Name ="&CH_Name"
AND SHA ="&SHA";
BY CH_Number CH_Name SHA;
COLUMN ("CH Number: &CH_Number CH Name: &CH_Name" ID TOTAL_COST NUMBER_OF_PURCHASES);
BY CH_Number CH_Name SHA NOTSORTED;
DEFINE ID / DISPLAY 'CUSTOMER ID' STYLE(COLUMN)={JUST=LEFT};
DEFINE TOTAL_COST / DISPLAY 'TOTAL COST' FORMAT=DOLLARr12.2 STYLE(COLUMN)={TAGATTR='format:$#,##0.00' JUST=RIGHT};
DEFINE NUMBER_OF_PURCHASES / DISPLAY 'AVERAGE NUMBER OF PURCHASES' FORMAT=COMMA10.2 STYLE(COLUMN)={TAGATTR='format:#,##0.00' JUST=RIGHT};
RUN;
PROC REPORT DATA=FINALDATASET3 NOWD MISSING ps=43 ls=108
style(report)={frame=box just=CENTER WIDTH=100%}
STYLE(header)={font_weight=bold background=lightgrey font_face=Arial font_size=8pt borderwidth=1px bordercolor=black just=CENTER}
STYLE(COLUMN)={background=white font_face=Arial font_size=7pt borderwidth=1px bordercolor=black just=CENTER}
STYLE(LINES)={JUST=C FONT_WEIGHT=BOLD FONT_SIZE=11PT};
WHERE CH_Number ="&CH_Number"
AND CH_Name ="&CH_Name"
AND SHA ="&SHA";
BY CH_Number CH_Name SHA;
COLUMN ("SHA: &SHA" ID TOTAL_COST NUMBER_OF_PURCHASES);
DEFINE ID / DISPLAY 'CUSTOMER ID' STYLE(COLUMN)={JUST=LEFT};
DEFINE TOTAL_COST / DISPLAY 'TOTAL COST' FORMAT=DOLLARr12.2 STYLE(COLUMN)={TAGATTR='format:$#,##0.00' JUST=RIGHT};
DEFINE NUMBER_OF_PURCHASES / DISPLAY 'AVERAGE NUMBER OF PURCHASES' FORMAT=COMMA10.2 STYLE(COLUMN)={TAGATTR='format:#,##0.00' JUST=RIGHT};
RUN;
%END;
%MEND;
%SPLIT
QUIT;
ODS PDF CLOSE;
ODS _ALL_ CLOSE;
ODS LISTING;
ODS RESULTS;
Hi Reeza,
To answer your first question, the second proc report does not generate any tables. For the second question, the BY statement adds nothing to the report or table other than it is used to identify the information that I want for each table on the same page. The tables look the same but differ by the content as the first table should have information for specific stores while the second table will have information for the region as a whole that the store is in....hope this clarifies things.
I am trying to use the #byvar for grouping by page but its not working in title statement for this report
TITLE2 f=Calibri j=c h=12pt '^{style[fontweight=bold] #byvar(CH_Number):} ^{style[fontweight=medium]#byval(CH_Number)}';
and with page in define statement along with group .
break after CH_Number/ page ;
its not grouping both stacked report by pages...what am i missing here ? Is it possible only
Problem could well be in the proc sql noprint; select ... as sqlobs is reporting 1 regardless of the number of records you might expect.
Example:
proc sql noprint;
select *
from sashelp.class;
quit;
%LET nValues = &sqlobs;
%put First Sql: &nvalues;
proc sql noprint;
create table work.junk as
select *
from sashelp.class;
quit;
%LET nValues = &sqlobs;
%put Second SQL: &nvalues;
Hi Reeza.....Thanks for your suggestion and help. That really helped in finding the problem. It turned out I had a unnecessary coma in the Proc SQL that creates the Finaldataset3 dataset. I removed it and everything works perfect. Thanks once again.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for 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.