Proc Report; custom column headers for additional columns within the across

[datadoneright]

I'm creating a report where I calculate changes between months based on the first across variable.  I have managed to insert the columns within the across and calculate the changes.  Now I want to title each type of change correctly.  The report will ultimately end up in Excel. I'm programming within SAS EG 7.15, (SAS BASE 9.4).

Currrent Report Top, Desired Report Bottom

Proc format;
value ex_column
	'31jul2019'd='Jul2019'
	'31aug2019'd='Aug2019'
	'15sep2019'd='Change' 
	;
Run;

Proc Report data=work.report_data_incomplete1 
(where=((crs_site = 'F') and datekey in ('31jul2019'd, '31aug2019'd) and RTTPart='Part_2')) 
out=work.proc_report1;

Title1 "TOTAL";
COLUMN ProvOrgName Label,Datekey,(Stats1);
define provorgnAME / ' ' group;
/*define sort_order /noprint across format=sort_order. preloadfmt order=data '';*/
define label / '' across order=data ;
define datekey / across nozero format=ex_column. preloadfmt '' order=data ;
define stats1 /'' ;

compute stats1;
_c4_=_c3_-_c2_;
_c7_=_c6_-_c5_;
_c10_=(_c9_-_c8_)/_c8_;
_c13_=_c12_-_c11_;
_c16_=_c15_-_c14_;
_c19_=_c18_-_c17_;
call define ('_c2_','format','4.1');
call define ('_c3_','format','4.1');
call define ('_c4_','format','4.1');
call define ('_c5_','format','4.1');
call define ('_c6_','format','4.1');
call define ('_c7_','format','4.1');
call define ('_c8_','format','bestd8.');
call define ('_c9_','format','bestd8.');
call define ('_c10_','format','percentn6.1');
call define ('_c11_','format','8.');
call define ('_c12_','format','8.');
call define ('_c13_','format','8.');
call define ('_c14_','format','8.');
call define ('_c15_','format','8.');
call define ('_c16_','format','8.');
call define ('_c17_','format','percentn6.1');
call define ('_c18_','format','percentn6.1');
call define ('_c19_','format','percentn6.1');
endcomp;

run;

[JosvanderVelden]

Can you detail what is your problem? Is it creating multiple lines in line 11 of the excel spreadsheet?

Have you read: http://support.sas.com/resources/papers/proceedings17/SAS0710-2017.pdf and/or https://communities.sas.com/t5/SAS-Procedures/Multiple-lines-in-a-PROC-REPORT-cell/td-p/312811

[datadoneright]

I want to change the column headings from 'Change' to specific type of change.  Ex.   _c4_ column heading = 'Change in Weeks',

_c10_ column heading = '% Change'. 

[Cynthia_sas]

Hi:
It looks like the header value "Change" is coming from the format you've created for the DATEKEY variable. You can't change the header variable as you envision with PROC REPORT. The COMPUTE block for a variable can only touch the data cells, not the header cells.

  You might be able to "fake" out the headers by making a separate fake DATEKEY value for every LABEL group, something like this -- I used SASHELP.PRDSALE Quarter=1 observations -- that gave me 3 months for each year to have across -- but I needed some "extra" observations, like this:

I know the dates in SASHELP.PRDSALE are always 01 of each month. So that means I can my fake observation for 31dec for each year and then assign the Header that I want to each years "fake" date. This allows me to use the format, as shown below:

I did not bother, in my PROC REPORT code to change formats or perform any calculations. You seem to have that part of the COMPUTE block working as you want.

But whether my approach will work for you depends on the structure of your data and how you built the DATEKEY variable.

Cynthia

[ballardw]

You should either provide some example data for your problem OR recast the problem to use one of the SAS supplied data set like SASHELP.Shoes or SASHELP.Class.