topic Wrap report using proc report in ODS and Base Reporting

Wrap report using proc report

LAtwood — Tue, 18 Oct 2011 18:38:25 GMT

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

Re: Wrap report using proc report

Cynthia_sas — Tue, 18 Oct 2011 22:33:45 GMT

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

Wrap report using proc report

LAtwood — Wed, 19 Oct 2011 13:22:22 GMT

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;

Re: Wrap report using proc report

Cynthia_sas — Wed, 19 Oct 2011 18:09:08 GMT

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;

Wrap report using proc report

LAtwood — Wed, 19 Oct 2011 18:45:21 GMT

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

Re: Wrap report using proc report

Cynthia_sas — Wed, 19 Oct 2011 19:18:39 GMT

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;

Wrap report using proc report

doakley — Wed, 19 Oct 2011 21:10:44 GMT

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

Wrap report using proc report

Cynthia_sas — Wed, 19 Oct 2011 21:20:28 GMT

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