Creating a table with Proc Report Sas 9.4

[Whitlea]

I am trying to create a table like this:

With this data but haven't been able to get the right results.  

data have;
input Type$	Month$ Y22 Y23 Y24	;

datalines;

A	July	512284	511247	537950			
A	Aug	  	467846	494399	494519		
A	Sep		456741	477914	585883		
A	Oct		441665	473957	554541		
A	Nov		352226	342704	720150		
A	Dec		584206	451450	366774		
B	July	32906	32906	92393	
B	Aug		35597	38010	40322
B	Sep		36720	42826	36455
B	Oct		51224	43689	46408
B	Nov		35151	52637	44017
C	July	346722	308021	293111			
C	Aug		328668	304660	270731		
C	Sep		683099	576012	527964		
C	Oct		992834	811538	751706		
C	Nov		910997	818528	748760		
C	Dec		724850	552243	749465		
 
;
run;


proc report data=have nowd;
column Type Y22 Y23 Y24,Month;
define Type/ group style=header;
define Month/ across style=header order=internal;
define Y22/ group style=header ;
define Y23/ group style=header ;
define Y24/ group style=header ;
run;

I have spent a lot of time playing around with the existing proc report but haven't had any luck.  Any help is greatly appreciated. 

Thanks!

[ballardw · Accepted solution]

Which parts are you having problems with?

I know that the repeated column headers are going to have problems as that is not a normal request from Proc report though an Break Before with a computed block might do.

But sort orders for character values as dates are problematic as well. You will need a Variable to hold values like "Y22" as Proc Report doesn't do vertically stacked variables.

Is the repeated header critical? If not, perhaps this would be sufficient and doesn't require rebuilding your data set:

proc tabulate data= have;
   class type;
   class month/order=data;
   var Y22 Y23 Y24;
   table type*(Y22 Y23 Y24)*sum=''*f=best8.,
         month
         /misstext=' '
   ;
run;

[Patrick]

You could reshape your data to make them more suitable for the report you're after.

data have;
	input Type$	Month$ Y22 Y23 Y24;
	datalines;
A July 512284 511247 537950
A Aug    467846 494399 494519
A Sep  456741 477914 585883
A Oct  441665 473957 554541
A Nov  352226 342704 720150
A Dec  584206 451450 366774
B July 32906 32906 92393
B Aug  35597 38010 40322
B Sep  36720 42826 36455
B Oct  51224 43689 46408
B Nov  35151 52637 44017
C July 346722 308021 293111
C Aug  328668 304660 270731
C Sep  683099 576012 527964
C Oct  992834 811538 751706
C Nov  910997 818528 748760
C Dec  724850 552243 749465  		
;
run;

data prep;
	set have;
	array vars {*} y22 y23 y24;
	do _i=1 to dim(vars);
		var_name=vname(vars[_i]);
		var_val =vars[_i];
		output;
	end;
	drop _i y22 y23 y24;
run;

%let sv_missing=%sysfunc(getoption(missing,keyword));
options missing=' ';
proc report data=prep nowd;
	column Type var_name var_val, Month;
	define Type/ group style=header;
	define Month/ across style=header order=data;
	define var_name/ group style=header ' ';
	define var_val/ display sum ' ';
run;
options &sv_missing;

[Kurt_Bremser]

Your main issue: having data in structure, see Maxim 19.

proc transpose data=have out=long;
by type month notsorted;
var y:;
run;

proc report data=long;
column type _name_ col1,month;
define type / "" group;
define _name_ / "" group;
define col1 / "" analysis;
define month / "" across order=data;
run;

PS avoid having tabs in code, particularly in DATALINES.

[SASKiwi]

Reorganising your data would make your report a lot easier:

data have;
input @1 Type $2. @5 Month monyy7. @12 Amount 6.;
format month monyy7.;
Year = year(month);
datalines;
A   Jul2022 512284
A   Jul2023 511247
A   Jul2024 537950
A   Aug2022 467846
A   Aug2023 494399
A   Aug2024 494519
A   Sep2022 456741
A   Sep2023 477914
A   Sep2024 585883		
A   Oct2022	441665
A   Oct2023 473957
A   Oct2024 554541		
A   Nov2022	352226	
A   Nov2023 342704
A   Nov2024 720150		
A   Dec2022	584206
A   Dec2023 451450
A   Dec2024 366774		
B   Jul2022	32906
B   Jul2023 32906
B   Jul2024 92393	
B   Aug2022	35597
B   Aug2023 38010
B   Aug2024 40322
B   Sep2022 36720
B   Sep2023 42826
B   Sep2024 36455
B   Oct2022	51224
B   Oct2023 43689
B   Oct2024 46408
B   Nov2022 35151
B   Nov2023 52637
B   Nov2024 44017
C   Jul2022	346722
C   Jul2023 308021
C   Jul2024 293111			
C   Aug2022 328668
C   Aug2023 304660
C   Aug2024 270731		
C   Sep2022	683099
C   Sep2023 576012
C   Sep2024 527964		
C   Oct2022	992834
C   Oct2023 811538
C   Oct2024 751706		
C   Nov2022	910997
C   Nov2023 818528	
C   Nov2024 748760		
C   Dec2022	724850
C   Dec2023 552243
C   Dec2024 749465		
;
run;

proc report data=have nowd;
column Type year Amount, Month ;
define Type/ group style=header;
define Year / group;
define Month / across style=header order=internal format = monname3.;
define Amount / ' ' sum;
run;

[Ksharp]

[EDIT]

It is time to show the power of PROC SQL + PROC REPORT skill.

data have;
	input Type$	Month$ Y22 Y23 Y24;
	datalines;
A July 512284 511247 537950
A Aug    467846 494399 494519
A Sep  456741 477914 585883
A Oct  441665 473957 554541
A Nov  352226 342704 720150
A Dec  584206 451450 366774
B July 32906 32906 92393
B Aug  35597 38010 40322
B Sep  36720 42826 36455
B Oct  51224 43689 46408
B Nov  35151 52637 44017
C July 346722 308021 293111
C Aug  328668 304660 270731
C Sep  683099 576012 527964
C Oct  992834 811538 751706
C Nov  910997 818528 748760
C Dec  724850 552243 749465  		
;
run;
proc format;
invalue mon(upcase)
'JAN'=1
'FEB'=2
'MAR'=3
'APR'=4
'MAY'=5
'JUN'=6
'JULY'=7
'AUG'=8
'SEP'=9
'OCT'=10
'NOV'=11
'DEC'=12
;
run;
proc sql;
create table temp as
select 1 as id1,type,month,'Y22' as year,put(sum(Y22),best32. -l) as value length=400
 from have
  group by type,month
union all
select 1 as id1,type,month,'Y23' as year,put(sum(Y23),best32. -l) as value
 from have
  group by type,month
union all
select 1 as id1,type,month,'Y24' as year,put(sum(Y24),best32. -l) as value
 from have
  group by type,month

union all
select 0 as id1,type,month,'09'x,month
 from (select distinct type from have),(select distinct month from have)

order by type,id1,year,input(month,mon.)
;
quit;

proc transpose data=temp out=report(drop=_name_) prefix=_;
by type id1 year;
var value;
id month;
run;

proc report data=report nowd;
columns ('Type' type) id1 year ('Month' _:) ;
define type/order ' ' style=header;
define id1/display noprint;
define year/display ' ' style=header;
define _:/display ' ' ;
compute id1;
if id1=0 then call define(_row_,'style','style=header');
endcomp;
run;

[whymath]

To simulate the screenshot style, try this:

proc transpose data=have out=tran;
  by type notsorted;
  var month y:;
run;

%let styles=backgroundcolor=cxedf2f9 font_weight=bold color=cx112277;
proc report data=tran style(header column)={just=center};
  column ("Type" type) _name_ ("Month" col1-col6);
  define type       /style(column)={&styles.} order order=data "";
  define _name_     /style(column)={&styles.} "";
  define col1-col6  /"";
  compute _name_;
    if _name_="Month" then do;
      _name_="";
      call define(_row_,"style","style={&styles.}");
    end;
  endcomp;
run;