BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
twildone
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
Break out the code, and get the second block working outside of a macro.

View solution in original post

7 REPLIES 7
Reeza
Super User
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?
twildone
Pyrite | Level 9

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.

Reeza
Super User
Break out the code, and get the second block working outside of a macro.
Cynthia_sas
SAS Super FREQ
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
Arora_S
Obsidian | Level 7

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

ballardw
Super User

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;
twildone
Pyrite | Level 9

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.

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
  • 7 replies
  • 1754 views
  • 0 likes
  • 5 in conversation