Group by Columns in PROC REPORT

jen123 — Wed, 06 Aug 2014 17:00:37 GMT

In my data, there is a variable called "TYPE", with 3 sub-types. I created a report using List Report Wizard in SAS EG. However, the resultant report view has each subtype on a page. See rows 1-41 in the attached file. I would like it to look like the table in row 49. I am added callouts/comments in the file regarding what calculations I need to do.

Type = needs to be grouped by columns

Version, Forecast Qualiifer, InitiativeType and Program = needs to be grouped by rows

Below is the code generated by SAS, with some modifications on my part.

Any help is much appreciated!

*****************************************************

data overall_report;

set AAA;

Version_2=Version;

InitiativeType_2=InitiativeType;

ForecastQualifier_2=ForecastQualifier;

run;

proc report data=jen1.overall_report nowd;

column Type Version ForecastQualifier InitiativeType Version_2 ForecastQualifier_2 InitiativeType_2 Program FYF14, SUM=FYF14_SUM FYF15, SUM=FYF15_SUM FYF16, SUM=FYF16_SUM FYF5Y, SUM=FYF5Y_SUM;

define Type / group format=$CHAR7. missing noprint order=formatted;

break after Type / page;

compute before _page_;

text1= ''||trim(left(put(Type,$CHAR7.)));

if Type=' ' then text1=' ';

line text1 $;

endcomp;

define Version / group format=$CHAR8. 'Version' missing noprint order=formatted;

compute Version;

if Version ne ' ' then hold1=Version;

if Version eq ' ' then Version=hold1;

endcomp;

define ForecastQualifier / group format=$CHAR14. 'ForecastQualifier' missing noprint order=formatted;

compute ForecastQualifier;

if ForecastQualifier ne ' ' then hold2=ForecastQualifier;

if ForecastQualifier eq ' ' then ForecastQualifier=hold2;

endcomp;

define InitiativeType / group format=$CHAR15. 'InitiativeType' missing noprint order=formatted;

compute InitiativeType;

if InitiativeType ne ' ' then hold3=InitiativeType;

if InitiativeType eq ' ' then InitiativeType=hold3;

endcomp;

define Version_2 / group 'Version' format=$CHAR8. missing order=formatted;

/***Calc the sub-totals***/

compute Version_2;

if upcase(_break_) eq "VERSION_2" then do;

Version_2=' ';

end;

if upcase(_break_) eq "VERSION_2" then do;

Version=' ';

end;

if upcase(_break_)="VERSION_2" then do;

call define("Version_2", 'style', 'style=[pretext="Subtotal "]');

end;

if _break_='_RBREAK_' then do;

call define("Version_2", 'style', 'style=[pretext="Total "]');

end;

endcomp;

define ForecastQualifier_2 / group 'ForecastQualifier' format=$CHAR14. missing order=formatted;

define InitiativeType_2 / group 'InitiativeType' format=$CHAR15. missing order=formatted;

define Program / group 'Program' format=$CHAR42. missing order=formatted;

define FYF14 / analysis SUM 'FYF14' format=MILLION. missing;

define FYF14_SUM / format=MILLION.;

define FYF15 / analysis SUM 'FYF15' format=MILLION. missing;

define FYF15_SUM / format=MILLION.;

define FYF16 / analysis SUM 'FYF16' format=MILLION. missing;

define FYF16_SUM / format=MILLION.;

define FYF5Y / analysis SUM 'FYF5Y' format=MILLION. missing;

define FYF5Y_SUM / format=MILLION.;

/***Add Grand Total Row***/

break before Version / summarize;

/***Add Sub-Total Rows***/

break before Version_2 / summarize;

run;

quit;

TITLE; FOOTNOTE;

Re: Group by Columns in PROC REPORT

Cynthia_sas — Thu, 07 Aug 2014 01:20:14 GMT

Hi:

I can't see your picture because it is still being scanned for viruses. But your code explicitly uses PAGE processing. So have you tried taking out the PAGE on the BREAK statement?

Cynthia

** from your posted code;

break after Type / page;

compute before _page_;

text1= ''||trim(left(put(Type,$CHAR7.)));

if Type=' ' then text1=' ';

line text1 $;

endcomp;

Re: Group by Columns in PROC REPORT

jen123 — Thu, 07 Aug 2014 11:57:06 GMT

Hi Cynthia - I do not know why it takes so long for the virus scanning.

I had actually tried taking out the entire line: break after Type / page; and it still didn't do what I needed.

As a "workaround", since I am limited on time, I separated the file into datasets by type and then will attempt to use PROC Report.

I am stuck on this one part. For my workaround, I created these columns:

FYF12B

FYF13B

FYF14B

FYF15B

FYF12C

FYF13C

FYF14C

FYF15C

If it Is possible, how do I create these column headers:

BENEFIT				COST
FYF12	FYF13	FYF14	FYF15	FYF12	FYF13	FYF14	FYF15

Re: Group by Columns in PROC REPORT

RW9 — Thu, 07 Aug 2014 12:24:53 GMT

File upload is not working at the moment.

In response to your question, to get headings like that they are called across headers. So:

columns ('BENEFIT' FYFB12-FYFB15) ('COST' FYF12C-FYF15C);

This will by default provide centered headers. You can however change either the template you are using, or put a style command in to adjust alignments.

topic Re: Group by Columns in PROC REPORT in SAS Enterprise Guide

Group by Columns in PROC REPORT

Re: Group by Columns in PROC REPORT

Re: Group by Columns in PROC REPORT

Re: Group by Columns in PROC REPORT