Group by Columns in PROC REPORT

[jen123]

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;

[Cynthia_sas]

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;

[jen123]

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

[RW9]

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.