The SAS Output Delivery System and reporting techniques

URGENT HELP - PROC REPORT

Accepted Solution Solved
Reply
Regular Contributor
Posts: 157
Accepted Solution

URGENT HELP - PROC REPORT

Hi,

I am using Proc report and in my data set there are no page break variables.I am using the below code.

After it is going from first page to second page number is not changing.

If i use a variable to create a page break then the All counties is printing after every page.All counties is a summarized value and it should print in the last page.

How to over come this.

ods pdf file =xxxxxxx;

PROC REPORT DATA=POP SPLIT='*'

  STYLE(REPORT)={WIDTH=100% RULES=GROUP FRAME=VOID FONT_WEIGHT=MEDIUM FONT_SIZE=8PT  FONT_FACE = "Courier New" ASIS=ON }

  STYLE(COLUMN)={BACKGROUND=WHITE FOREGROUND=black FONT_FACE="Courier New" FONT_WEIGHT=MEDIUM FONT_SIZE=8pt };

 

  COLUMN SSN LASTNAME CITY STATE COUNTY  TOTC GENC GENPCT;

  DEFINE SSN / DISPLAY STYLE={WIDTH=10.0% JUST = L}

  DEFINE LASTNAME / DISPLAY STYLE={WIDTH=24.0% JUST = L};

  DEFINE CITY / DISPLAY   STYLE={WIDTH=12.0% JUST = L};

  DEFINE STATE / DISPLAY STYLE={WIDTH=9% JUST = C};

  DEFINE  COUNTY / DISPLAY   STYLE={WIDTH=15.0% JUST = L};

  DEFINE  TOTC / ANALYSIS STYLE={WIDTH=9% JUST = R};

  DEFINEGENC / ANALYSIS STYLE={WIDTH=11% JUST = R};

  DEFINE GENPCT     / DISPLAY  STYLE={WIDTH=9% JUST = R};

  DEFINE GENPCT / COMPUTED FORMAT=PERCENT9.2 STYLE={WIDTH=9% JUST = R };

  RBREAK AFTER / SUMMARIZE STYLE={FONT_STYLE=ROMAN} ;

  COMPUTE GENPCT;

  IF TOTC.SUM=0.00 THEN  DO

        GENPCT=0.00;

  END;

  ELSE DO;

  GENPCT=GENC.SUM/TOTC.SUM;

  END;

  ENDCOMPUTE;

  COMPUTE COUNTY;

  IF  _BREAK_ = '_RBREAK_' then

         COUNTY = '*ALL COUNTIES*';

  ENDCOMP;

COMPUTE BEFORE _PAGE_  /style={asis=on font_face="Courier New" font_size=8PT  };

  LINE @129 "PAGE~{nbspace 9}: ~{thispage}" ;

    ENDCOMP;

    RUN;

    ODS PDF TEXT = "~S={JUST=L leftmargin=4.4IN FONT_SIZE=8PT FONT_FACE='Courier New'}~_~3n END  ";

ods pdf close;


Accepted Solutions
Solution
‎04-26-2012 10:33 AM
SAS Super FREQ
Posts: 8,868

Re: URGENT HELP - PROC REPORT

Hi:

  You can't have more than 1 DEFINE statement for an item. So your 2 DEFINES, both for GENPCT will be an issue. So that is one problem with your posted code. If you want to get a summary of any kind for GENPCT at the break, you will need to define it with SUM or ANALYSIS and get rid of the DEFINE with COMPUTED. Or, if GENPCT is NOT in the data and you truly are calculating it, then get rid of the DEFINE that says DISPLAY as the usage and leave the DEFINE with COMPUTED -- but only have 1 DEFINE statement for GENPCT. Any item on the COLUMN statement can have a COMPUTE block.

  Honestly, I can't replicate your stated behavior. If I use the code below, which is putting exactly 35 obs on each page, using the PGCNTR variable, then I get a summarized line only at the BREAK.

  I can't agree with you that PROC REPORT has BIG limitations. Yes, it does have limitations, but, as I remember, you wanted previously to apply a format to the SAS page number from {thispage} and that is not a limitation of PROC REPORT -- that is a limitation of ODS. The fact that you can make your own dummy page number and then use PROC REPORT to put that dummy page number on the report implies to me that PROC REPORT is quite powerful and flexible. There are some report generators that don't give you very much control over your headers and titles, so I think you have a lot of control.

  The fact that @129 might not work as you desire is not really a limitation of PROC REPORT, either. It is the difference between writing to the LISTING destination and writing to other ODS destinations. In the LISTING destination, your output "page" is like a piece of graph paper, where every tiny print position is exactly the same size and you can write to a fixed position because you know exactly how many characters will fit on a page, both horizontally and vertically. However, in the ODS world, some destinations, like HTML are not paged destinations, so there is no concept of a page or page breaks or page numbers. In other destinations, the letter 'i' takes up way less space on a printed line than the letter 'w':

iiiiiiiiiiiiiiiiiiii  (20 'i')

wwwwwwwwwwwwwwwwwwww (20 'w')

ssssssssssssssssssss (20 's')

abcdefghijklmnopqrst  (20 letters of the alphabet)

supercalifragilisticexpealidocious  (random word with 34 characters)

So, where is @10 on every line???? Does it "line up"?

   I have attached screenshots that show just about everything your program does -- I didn't have any percent data, but I have a calculated page number in the COMPUTE BEFORE; I have a fake page break variable; and I have a summary line only on the last page.

  One difference between my program and yours is that I use a BREAK BEFORE PGCNTR -- because if I don't my summary line from the RBREAK will be on a separate page. But, I do NOT get a summary line on every page, as you can see from the screenshot of the end of page 1 and the start of page 2. Now, if you did a PROC PRINT on a data set created by PROC REPORT, you would see a line for every time you break (such as the COMPUTE BEFORE _PAGE_ and the BREAK) -- but I don't see OUT=.

  This may be an issue with something about your data or something else you're doing with your code. You might want to work with Tech Support on this one...I don't understand how your posted code is getting a summary on every page. I could understand it if you had a BY statement -- but I'm stumped.

cynthia


data pageit;
  set sashelp.shoes(obs=100);
  retain pgcntr 1;
  output;
  if mod(_n_,35) = 0 then pgcntr +1;
run;
  
options nodate nonumber orientation=portrait
        topmargin=1in bottommargin=1in
        leftmargin=1in rightmargin=1in;

  title; footnote;
    
ods pdf file='c:\temp\fake_page_break.pdf' notoc style=journal;
ods escapechar='~';
proc report data=pageit nowd
     style(report)={width=100% rules=group frame=void
                    font_weight=medium font_size=8pt
                    font_face="Courier New"}
     style(header)={font_weight=medium font_size=8pt
                    font_face="Courier New"}
     style(column)={font_weight=medium font_size=8pt
                    font_face="Courier New"};
  column pgcntr region subsidiary product sales;
  define pgcntr / order  /* NOPRINT */;
  define region / display;
  define subsidiary / display;
  define product / display;
  define sales / sum;
  compute before _page_ /
          style={font_face="Courier New"
                 font_size=8PT  just=r};
  LINE "PAGE~{nbspace 9}:" pgcntr;
  endcomp;
  break before pgcntr / page ;
  rbreak after / summarize style={font_weight=bold};
  compute after;
    region='All Counties';
  endcomp;
run;
ods pdf close;


end_of_report.jpgbottom_page1.jpg

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,868

Re: URGENT HELP - PROC REPORT

Hi:

  I'm not sure when the page break routine kicks into action and increases the internal page number value. I don't know that I have ever tried to use ESCAPECHAR+{thispage} in a LINE statement such as you're trying. I have only used ESCAPECHAR+{thispage} in a TITLE or FOOTNOTE statement. The LINE statement "happens" inside the main portion of the document -- I just don't think of that as being the right place for page numbers.

  I can run the program below to see essentially the same behavior that you are observing and I think the fact that it doesn't work means that {thispage} doesn't resolve in the body of the document or inside the boundary of the report table -- somehow the 1 gets there because that is the beginning value of {thispage}, but it is not a dynamically generated value in the body of the document -- not in the same way it is put into the title or footnote area. It seems to me that the page number only populates in the traditional page number areas -- the top or bottom of the page. To me, that implies you are going to have to stick with using the page number in the TITLE statement.

  I can't envision a situation where the page number would look good sitting above the table, as you show. Also there is no such thing as using position pointer control (such as @129 in ODS PDF). Line pointer control, such as @129 only works really well in the LISTING window.

  I don't think there is a workaround for what you are trying to do. You might want to check with Tech Support -- but my recommendation would be to stick with {thispage} in the TITLE statement.

cynthia

options nodate nonumber;

     

ods escapechar='~';

ods pdf file='c:\temp\use_thispage.pdf';

proc report data=sashelp.shoes nowd;

  title j=r font='Courier New' h=8pt 'In title: PAGE: ~{thispage}';

  column region product inventory sales;

  compute before _page_/

          style={asis=on font_face="Courier New"

                 font_size=8PT  };

  LINE @129 "PAGE~{nbspace 9}: ~{thispage}" ;

  endcomp;

run;

ods _all_ close;

Regular Contributor
Posts: 157

Re: URGENT HELP - PROC REPORT

Posted in reply to Cynthia_sas

Hi Cynthia,

i cannot use title as it will not print it where i want them.

Proc report has a lot of limitations.

One more question

In my report i want to print county totals in the last page.I am using a dummy variable to force page break.

It is printing the county totals for each page.

Can you pleasr let me know how to avoid it.

Super User
Posts: 10,041

Re: URGENT HELP - PROC REPORT

You are on the right way.

How do you break a new page ? by BREAK statement or LINE statement, they both can re-set sum be zero.

if you will post some sample data and code. I think Cynthia will give you answer.

Ksharp

SAS Super FREQ
Posts: 8,868

Re: URGENT HELP - PROC REPORT

Hi:

In your code, I did not see a dummy variable for breaking....is the code you posted the same as what you are describing? Here's your COLUMN statement

COLUMN SSN LASTNAME CITY STATE COUNTY  TOTC GENC GENPCT;

...which one of these is your dummy break variable??? Usually, when you want to do BREAK processing with a dummy variable, it is because you want to use the dummy variable with a BREAK statement.

I do see that you have an RBREAK statement. The only way I have ever seen an RBREAK statement produce a summary line in the "wrong" place is when you have BY statements in your code -- since every BY group is treated as a whole entity, using BY with RBREAK can cause a summary line at the end of every BY group. But I don't see a BY statement in your code.

So, my question to you is what does your data look like???? Which variable is your dummy variable??? Is the code you posted the same as the code that's not working? If you opened a track with Tech Support, they could look at all your code and all your data and help you with your exact program and your exact data.

cynthia

Regular Contributor
Posts: 157

Re: URGENT HELP - PROC REPORT

Posted in reply to Cynthia_sas

Hi Cynthia,

I appreciate your patience and support.

In the below code my dummy variable is BRK.It contains count of observations.When the count is 25 it will break the Page.I am using it to print page number also.I highlighted the dummy variable in BOLD.

Can you please let me know ur opinion.

ods pdf file =xxxxxxx;

PROC REPORT DATA=POP SPLIT='*'

  STYLE(REPORT)={WIDTH=100% RULES=GROUP FRAME=VOID FONT_WEIGHT=MEDIUM FONT_SIZE=8PT  FONT_FACE = "Courier New" ASIS=ON }

  STYLE(COLUMN)={BACKGROUND=WHITE FOREGROUND=black FONT_FACE="Courier New" FONT_WEIGHT=MEDIUM FONT_SIZE=8pt };

  COLUMN BRK SSN LASTNAME CITY STATE COUNTY  TOTC GENC GENPCT;

  DEFINE BRK / ORDER NOPRINT

  DEFINE SSN / DISPLAY STYLE={WIDTH=10.0% JUST = L}

  DEFINE LASTNAME / DISPLAY STYLE={WIDTH=24.0% JUST = L};

  DEFINE CITY / DISPLAY   STYLE={WIDTH=12.0% JUST = L};

  DEFINE STATE / DISPLAY STYLE={WIDTH=9% JUST = C};

  DEFINE  COUNTY / DISPLAY   STYLE={WIDTH=15.0% JUST = L};

  DEFINE  TOTC / ANALYSIS STYLE={WIDTH=9% JUST = R};

  DEFINEGENC / ANALYSIS STYLE={WIDTH=11% JUST = R};

  DEFINE GENPCT     / DISPLAY  STYLE={WIDTH=9% JUST = R};

  DEFINE GENPCT / COMPUTED FORMAT=PERCENT9.2 STYLE={WIDTH=9% JUST = R };

  RBREAK AFTER / SUMMARIZE STYLE={FONT_STYLE=ROMAN} ;

BREAK AFTER BRK / PAGE;

  COMPUTE GENPCT;

  IF TOTC.SUM=0.00 THEN  DO

        GENPCT=0.00;

  END;

  ELSE DO;

  GENPCT=GENC.SUM/TOTC.SUM;

  END;

  ENDCOMPUTE;

  COMPUTE COUNTY;

  IF  _BREAK_ = '_RBREAK_' then

         COUNTY = '*ALL COUNTIES*';

  ENDCOMP;

COMPUTE BEFORE _PAGE_  /style={asis=on font_face="Courier New" font_size=8PT  };

  LINE @129 "PAGE~{nbspace 9}:" BRK  ;

    ENDCOMP;

    RUN;

    ODS PDF TEXT = "~S={JUST=L leftmargin=4.4IN FONT_SIZE=8PT FONT_FACE='Courier New'}~_~3n END  ";

ods pdf close;

SAS Super FREQ
Posts: 8,868

Re: URGENT HELP - PROC REPORT


Hi:

  Thanks for posting the updated program. Now it comes down to your data. If you actually SHOW the values of BRK, what are they? I don't understand how BRK can be both a dummy page number AND a count of observations. When I use a fake page variable for breaking, every obs on page 1 has a value of 1, every obs for dummy page 2 has a value of 2, every obs for dummy page 3 has a value of 3, etc, etc -- this is not a count of observations. I know that I have posted code to create a dummy break variable before and there are examples posted elsewhere, too. I think you are close to what you want, although it seems a lot of work just to get the fake page number sitting on top of the table. You might still be able to work it out with the TITLE statement and {thispage} if you adjusted the outputwidth of the TITLE to be the same as the outputwidth of the table. I am on a plane today and not able to get to the computer with SAS -- so either double check your values for BRK and adjust them accordingly or try the TITLE approach again. You are removing most of the interior table lines, (something JOURNAL style would do too), so I believe that you could still make TITLE work.

cynthia

Regular Contributor
Posts: 157

Re: URGENT HELP - PROC REPORT

Posted in reply to Cynthia_sas

Hi Cynthia,

Yes you are right.It is not count of observations sorry about that.It contains value of 1 on first page and value of 2 on second page.I think in my case title will not work as you see in my code i have to print them in certain positions.Can i print title statement in the desired positions i want.

If you have an example can you please post it.

I appreciate your support.

SAS Super FREQ
Posts: 8,868

Re: URGENT HELP - PROC REPORT

HI,

At airport and still not on computer, but here's the thing. IF @129 works at all it will be pure luck for positioning. Generally, pointer controls in your LINE statement do not work reliably with ODS.

So, I will look for the posting. Generally i use BRKVAR or FAKEPG as var names so you might search for those.

I think there is even a Tech Support note that has code.

AS far as absolute positioning like @129 with titles -- no, but i don't expect line to really reliably work either, you might find that the string wavers or shifts position frm page to page.

Cynthia

Regular Contributor
Posts: 157

Re: URGENT HELP - PROC REPORT

Posted in reply to Cynthia_sas

Hey Cynthia,

If i want to print the summarized totals at the end of report for the three variables

DEFINE  TOTC / ANALYSIS STYLE={WIDTH=9% JUST = R};

  DEFINEGENC / ANALYSIS STYLE={WIDTH=11% JUST = R};

  DEFINE GENPCT     / DISPLAY  STYLE={WIDTH=9% JUST = R};

  DEFINE GENPCT / COMPUTED FORMAT=PERCENT9.2 STYLE={WIDTH=9% JUST = R };

i want to print the summarized totals in the last page.When i am using dummy page variable to break the summarized totals are printing at the end of each page.How to avoid it and print it in the last page.

Can you please let me know

Regular Contributor
Posts: 157

Re: URGENT HELP - PROC REPORT

Hi Cynthia,

The one i am trying to do is not possible in proc report i think.I tried different ways but it didn't work out.I think it is a big limitation in proc report.

If you have any solution how to print the summary totals in last page while using dummy variable to force page break

Solution
‎04-26-2012 10:33 AM
SAS Super FREQ
Posts: 8,868

Re: URGENT HELP - PROC REPORT

Hi:

  You can't have more than 1 DEFINE statement for an item. So your 2 DEFINES, both for GENPCT will be an issue. So that is one problem with your posted code. If you want to get a summary of any kind for GENPCT at the break, you will need to define it with SUM or ANALYSIS and get rid of the DEFINE with COMPUTED. Or, if GENPCT is NOT in the data and you truly are calculating it, then get rid of the DEFINE that says DISPLAY as the usage and leave the DEFINE with COMPUTED -- but only have 1 DEFINE statement for GENPCT. Any item on the COLUMN statement can have a COMPUTE block.

  Honestly, I can't replicate your stated behavior. If I use the code below, which is putting exactly 35 obs on each page, using the PGCNTR variable, then I get a summarized line only at the BREAK.

  I can't agree with you that PROC REPORT has BIG limitations. Yes, it does have limitations, but, as I remember, you wanted previously to apply a format to the SAS page number from {thispage} and that is not a limitation of PROC REPORT -- that is a limitation of ODS. The fact that you can make your own dummy page number and then use PROC REPORT to put that dummy page number on the report implies to me that PROC REPORT is quite powerful and flexible. There are some report generators that don't give you very much control over your headers and titles, so I think you have a lot of control.

  The fact that @129 might not work as you desire is not really a limitation of PROC REPORT, either. It is the difference between writing to the LISTING destination and writing to other ODS destinations. In the LISTING destination, your output "page" is like a piece of graph paper, where every tiny print position is exactly the same size and you can write to a fixed position because you know exactly how many characters will fit on a page, both horizontally and vertically. However, in the ODS world, some destinations, like HTML are not paged destinations, so there is no concept of a page or page breaks or page numbers. In other destinations, the letter 'i' takes up way less space on a printed line than the letter 'w':

iiiiiiiiiiiiiiiiiiii  (20 'i')

wwwwwwwwwwwwwwwwwwww (20 'w')

ssssssssssssssssssss (20 's')

abcdefghijklmnopqrst  (20 letters of the alphabet)

supercalifragilisticexpealidocious  (random word with 34 characters)

So, where is @10 on every line???? Does it "line up"?

   I have attached screenshots that show just about everything your program does -- I didn't have any percent data, but I have a calculated page number in the COMPUTE BEFORE; I have a fake page break variable; and I have a summary line only on the last page.

  One difference between my program and yours is that I use a BREAK BEFORE PGCNTR -- because if I don't my summary line from the RBREAK will be on a separate page. But, I do NOT get a summary line on every page, as you can see from the screenshot of the end of page 1 and the start of page 2. Now, if you did a PROC PRINT on a data set created by PROC REPORT, you would see a line for every time you break (such as the COMPUTE BEFORE _PAGE_ and the BREAK) -- but I don't see OUT=.

  This may be an issue with something about your data or something else you're doing with your code. You might want to work with Tech Support on this one...I don't understand how your posted code is getting a summary on every page. I could understand it if you had a BY statement -- but I'm stumped.

cynthia


data pageit;
  set sashelp.shoes(obs=100);
  retain pgcntr 1;
  output;
  if mod(_n_,35) = 0 then pgcntr +1;
run;
  
options nodate nonumber orientation=portrait
        topmargin=1in bottommargin=1in
        leftmargin=1in rightmargin=1in;

  title; footnote;
    
ods pdf file='c:\temp\fake_page_break.pdf' notoc style=journal;
ods escapechar='~';
proc report data=pageit nowd
     style(report)={width=100% rules=group frame=void
                    font_weight=medium font_size=8pt
                    font_face="Courier New"}
     style(header)={font_weight=medium font_size=8pt
                    font_face="Courier New"}
     style(column)={font_weight=medium font_size=8pt
                    font_face="Courier New"};
  column pgcntr region subsidiary product sales;
  define pgcntr / order  /* NOPRINT */;
  define region / display;
  define subsidiary / display;
  define product / display;
  define sales / sum;
  compute before _page_ /
          style={font_face="Courier New"
                 font_size=8PT  just=r};
  LINE "PAGE~{nbspace 9}:" pgcntr;
  endcomp;
  break before pgcntr / page ;
  rbreak after / summarize style={font_weight=bold};
  compute after;
    region='All Counties';
  endcomp;
run;
ods pdf close;


end_of_report.jpgbottom_page1.jpg
Regular Contributor
Posts: 157

Re: URGENT HELP - PROC REPORT

Posted in reply to Cynthia_sas

Hi Cynthia,

I apologize for the inconvenience.I am a DATA _NULL_ guy may be due to the ease of that i commented on proc report.The problem i had is with data.Due to that it is creating summary for each page.It worked for me now.

one last question.

As you can see my GEN_PCT which is my last column i am having that in my data.So i used

Define GEN_PCT / ANALYSIS;

when i use summarize in the bottom of the report it is adding up and displaying the percentage.

For example :

Genpct

90.00%

50.00%

30.00%

170%

It is displaying 170 %. So then i used this

DEFINE GENPCT / DISPLAY

Computer after

county='all counties'

if totc.sum=0

then do;

genpct=0;

else do;

genpct=genc/totc;

end;

can you please let me know ur opinion.

endcomp;

when i do this it is not displaying any value in the summary line of GENPCT

it is displaying like this

Genpct

90.00%

50.00%

30.00%

it is not printing any value it is leaving it blank

Regular Contributor
Posts: 157

Re: URGENT HELP - PROC REPORT

Hey Cynthia,

i got it.i used a compute block.i am getting the result i want

SAS Super FREQ
Posts: 8,868

Re: URGENT HELP - PROC REPORT


Ah, good to know! I thought the COMPUTE block would be the solution -- either that or something with your data. Glad you got it working!

cynthia

🔒 This topic is solved and locked.

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

Discussion stats
  • 14 replies
  • 1524 views
  • 0 likes
  • 3 in conversation