topic Re: Proc Report; custom column headers for additional columns within the across in ODS and Base Reporting

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

datadoneright — Fri, 27 Sep 2019 10:42:27 GMT

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;

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

JosvanderVelden — Fri, 27 Sep 2019 13:29:09 GMT

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

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

datadoneright — Fri, 27 Sep 2019 13:44:23 GMT

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'.

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

ballardw — Fri, 27 Sep 2019 15:21:03 GMT

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.

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

Cynthia_sas — Fri, 27 Sep 2019 19:29:26 GMT

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