The SAS Output Delivery System and reporting techniques

RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

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;


Accepted Solutions
Solution
‎01-04-2016 07:54 PM
Super User
Posts: 17,829

Re: RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

Break out the code, and get the second block working outside of a macro.

View solution in original post


All Replies
Super User
Posts: 17,829

Re: RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

Does your second report get generated if you hardcode in the values?

Also, I'm not that familiar with proc report, but if you have a single ch_name/number/sha does the BY statement add anything to the report?
Regular Contributor
Posts: 222

Re: RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

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.

Solution
‎01-04-2016 07:54 PM
Super User
Posts: 17,829

Re: RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

Break out the code, and get the second block working outside of a macro.
SAS Super FREQ
Posts: 8,743

Re: RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

Hi,
In addition to Reeza's suggestion, I suggest you check the messages in the log. Since you are using a WHERE statement against FINALDATASET3 in the second PROC REPORT, are you even getting observations selected for the second PROC REPORT? The log will indicate that to you. The only other reason (other than bad WHERE conditions) that I can think for not getting output from the second step is that you have a syntax error of some kind, which would also be evident in the SAS log.

In addition, there are some odd things about your code, several of which I noted in my response to your previous post:
-- LS and PS are ignored by ODS PDF -- don't know why you still have them
-- TAGATTR is used by ODS TAGSETS.EXCELXP, not by ODS PDF, so your SAS formats will be controlling the format in the cell, not TAGATTR.

cynthia
Contributor
Posts: 35

Re: RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

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

Super User
Posts: 10,500

Re: RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

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;
Regular Contributor
Posts: 222

Re: RE: Construct stacked tables from 2 PROC REPORTS using ODS PDF

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 623 views
  • 0 likes
  • 5 in conversation