Wrap report using proc report

[LAtwood]

I have attached the code that I am working with.  I was wondering if its possible to fit all data on one page using proc report?  For example, I am working with two columns (cc, labor hours) and grouping/ paging it by the Shop #.  What happens is, if there are a lot of CC's with labor hours, it moves to another page and because it is only two columns I would like it to finish listing the data on the same page.

Would like to see this:

Shop 301

CC     Labor Hours                 CC     Labor Hours

053     4.0                             065     13.0  

239     20.0                           244      8.0    

314     2.0

506     16.0

516     4.0

**End of Page**

Instead of this:

Shop 301

CC     Labor Hours

053     4.0

239     20.0

314     2.0

506     16.0

516     4.0

065     13.0

244     8.0

Thanks for your help

Lori

[Cynthia_sas · Accepted solution]

Hi:

    ODS PDF supports the COLUMNS=option, which creates multiple columns in your PDF result file. For example, you should be able to run the code below in EG as just an EG code node (without being a stored process).

ods _all_ close;

ods pdf file='c:\temp\mult_columns.pdf' columns=3;

  proc report data=sashelp.shoes nowd;

     where region in ('Asia', 'Canada', 'Pacific','Western Europe', 'United States');

     column region sales;

     define region / display;

     define sales / display f=dollar12.2;

  run;

ods pdf close;

However, I cannot guarantee that this code, as a stored process, using COLUMNS=3 or COLUMNS=2 would work in all client applications (such as Web Report Studio, or the SAS Add-in for Microsoft Office). You'd have to do further testing to determine that. However, the COLUMNS= option will create multi-column output (what you call "wrapping").

cynthia

[LAtwood]

Thanks Cynthia, using the columns=option worked.  However, the data is no longer paging by the shop number.  If the shop data only has two columns of information and I said columns=3 it still gives it three columns and grabs the data from the next shop number.  Am I doing something wrong to page it by Shop number?  Or will it always give it three columns no matter how I try to do a page by?

options orientation=landscape center number nobyline;

ods pdf file=_WEBOUT notoc columns=3;

proc report data=WORK.QUERY_FOR_PAY_SHOP_CARD_EXTRACT nowd;

column shop cc SUM_of_hrs_worked;

by shop;

define shop / group noprint;

define cc / display 'CC';

define SUM_of_hrs_worked / sum 'Labor Hours';

break after shop / page summarize;

run;

ods _all_ close;

[Cynthia_sas]

Hi:

  Interesting...my test with BY group processing worked to create columns -and- a new page for every PRODUCT. I ran my test using SAS 9.3. You might want to run the attached code and if you do not get a new page for every product, I'd recommend that you open a track with Tech Support. When I run this code in SAS 9.3, I get 8 pages -- one page for every product. I used the NOBYLINE option and #BYVAL1 to avoid having the BYLINE sit above each column. But in my tests, BY group processing did work.

cynthia

ods _all_ close;

** Use BY group processing;

proc sort data=sashelp.shoes out=shoes;

  by product region;

run;

options nobyline;

ods pdf file='c:\temp\mult_columns_by.pdf' columns=2;

  proc report data=shoes nowd;

     by product;

     title '(columns=2) BY Group For Product: #byval1';

     column product region sales;

     define product / order;

     define region / display;

     define sales / sum f=dollar16.2;

     break after product / page summarize;

  run;

ods pdf close;

title;

options byline;

[LAtwood]

Cynthia,

I ran your code and it does page it correctly.  I think the reason my code is not working because some data only fits in one column for the "by column" and other times it needs three columns.

I have attached the output that I receive in my first posting. titled do.pdf

[Cynthia_sas]

Hi:

  ODS and SAS don't operate like a word processor or a publishing program. You could be right that the number of columns needed for a BY group are messing it up. Your alternative, at this point, would be to "simulate" BY group processing by having a separate PROC REPORT step for every SHOP code and then set the COLUMNS= appropriately for each code. Using SAS Macro could make this easier to package the PROC REPORT code, but the general outline would be something like this. Note that since I am using separate steps for every group, I have changed the BREAK AFTER to RBREAK and removed the BY statement from each step. I had to arbitrarily fiddle with the SHOES data and the number of obs in each step to show the different COLUMNS= settings, but you should get the general idea.

cynthia

ods _all_ close;

** Use BY group processing;

proc sort data=sashelp.shoes out=shoes;

  by product region;

run;

options orientation=landscape topmargin=.5in bottommargin=.5in

        leftmargin=.5in rightmargin=.5in;

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

ods pdf columns=2;

  proc report data=shoes(obs=45) nowd;

    ** approx 20+ obs per column, so 45 obs = 2 columns;

  where product = "Boot";

  title '(columns=2) Product is Boot (45 obs)';

  column product region sales;

  define product / order noprint;

  define region / display;

  define sales / sum f=dollar16.2;

  rbreak after  / summarize;

  run;

  ods pdf columns=3;

  proc report data=shoes(obs=70) nowd;

  where product contains "Casual" ;

  title '(columns=3) Enough Casual Shoes (70 obs) for 3 columns';

  column product region sales;

  define product / order noprint;

  define region / display;

  define sales / sum f=dollar16.2;

  rbreak after  / summarize;

  run;

  ods pdf columns=1;

  proc report data=shoes(obs=20) nowd;

  where product = "Sandal";

  title '(columns=1) Sandal obs for 1 column';

  column product region sales;

  define product / order noprint;

  define region / display;

  define sales / sum f=dollar16.2;

  rbreak after  / summarize;

  run;

ods pdf close;

title;

[doakley]

Cynthia,

I've added the COLUMNS= statement to an ODS statement, and it works, but with an undesired side effect-- it wipes out a footnote that is supposed to follow the data, even though the data in the column doesn't reach that far down the page. Can this be controlled with some kind of margins modifier to the ods statement?

Thanks,

David

[Cynthia_sas]

Hi, as far as I know, COLUMNS= only works with RTF and PDF. There is a known issue with PDF for SAS 9.2, as reported here: http://support.sas.com/kb/37/575.html . The workaround is to remove the COLUMNS= option from ODS PDF.

Perhaps this is what you are encountering. I ran a test in SAS 9.3 and do get both columns and footnotes in the output PDF file. The note says this issue was fixed in SAS 9.2 TS2. So, if you are using SAS 9.2 TS2 and are still encountering the issue, you should open a track with Tech Support.

cynthia