PROC REPORT - Adding columns between ACROSS variable categories/ levels?

[Mertthew]

Hello All,

First time posting because I'm a bit stuck.

Basically, I am using PROC REPORT to output an excel .xls in which various patient types N and % statistics are displayed across Australian states.

While I am able to successfully output this table, to cutdown on the various manual formatting touches when added to my report, I was keen to see if it were possible to add additional columns between the levels of across variable (each state) that, unlike adding a nested dummy variable, would also extend between each category heading.

e.g.

My original code:

PROC REPORT data=MockUp headline style(report)={width=100%} split='@';
Columns patient_type state,(Sum Per);
	define patient_type/"Patient status" group order=data style(column)={just=left} style(header)={just=left};
	define state/"" across order=data style(column)={just=center} style(header)={just=center};
	define Sum/"No." style(column)={just=center} format=comma10. style={TAGATTR="format:###,###"};
	define Per/"%" style(column)={just=center} format=5.1 style={TAGATTR="format:##0.0"};
run;

Gives me this - note data has been redacted (it's a proc tabulate screenshot, but the proc report gives me basically the same): 

Currently my closet attempts have been modifying the column statement and adding a new define statement for a nested dummy column e.g.

PROC REPORT data=MockUp headline style(report)={width=100%} split='@';
Columns patient_type state,(dummy Sum Per);
	define patient_type/"Patient status" group order=data style(column)={just=left} style(header)={just=left};
	define state/"" across order=data style(column)={just=center} style(header)={just=center};
	define dummy/ " "; /* This adds a blank column but nested below each category header*/
	define Sum/"No." style(column)={just=center} format=comma10. style={TAGATTR="format:###,###"};
	define Per/"%" style(column)={just=center} format=5.1 style={TAGATTR="format:##0.0"};
Run;

Which gives me this:

It's close, but not what I really need formatting wise. Is it at all within the functionality using state as an ACROSS variable to insert intervening columns between each level (i.e. each state category)? Or possibly able to split the headings themselves?

Or is the only way to do it more laboriously by defining each state as a separate variable and then listing them manually, separate by multiple dummy columns?

Cheers for any assistance.

M

[andreas_lds]

Well, not easy, it may not be possible at all. Have you ever written your own templates? I have written some style-templates, but never tried to create a template defining the structure of a table.

Hopefully @Cynthia_sas has a better idea.

[Cynthia_sas]

Hi:

 Thanks for posting your code. However, without data, it is impossible to test or tweak any code. Also, it seems you want a blank column someplace, but I'm not sure WHERE you want the blank column. When you put the dummy variable inside the parentheses for the ACROSS structure, the ONLY place that the blank column can go is under each STATE value and before each SUM and PER value. The way PROC REPORT works is that the cell for the ACROSS variable, in this case, STATE, will span ALL of the variable headers under it. That means you can't force the header for the dummy variable to change the header for STATE. By the time the STATE headers have been written, PROC REPORT can't go back and change that row. So the next row is the nested columns and they are UNDER the STATE header. But I still don't understand what you want, really, what's the purpose of the blank, why not just make SUM and/or PER wider or change the cellpadding if you need more white space?

Cynthia

[Mertthew]

Hi Cynthia (and Everyone),

Thanks for the quick replies. Sorry, I can't share the actual data, but will create dummy data that will work with my code below as well as a mockup of desired appearance / output. 

I understand that the nesting the dummy column under the across variable means the formatting is already set in stone and can not be altered, that was just the result of tinkering and got me closest to what I wanted at this stage, although obviously falls short.

What I want (unformatted) looks like this:

I've highlighted the column gaps I want.

The reason I want them this way, rather than bearing with the dummy column solution or adding manually them after the fact is because given the guidelines my organisation has for presentation of tables within reports, I need the gaps. Having these gaps, assists with custom underlining that I do want i.e. having gaps between the states but not the stat headings like so:

Having SAS output my tables as above, already formatted, also avoids a great deal of manual tinkering with the spacing and buffer as suggested which could lead to human error, especially since I have about 50 odd tables to produce in this (and similar) formats/styles. Unfortunately my organisation, while wanting them a certain way, does not provide resources to automate this via code.

To assist, I've created and attached a 50-observation mockup raw dataset (MockUp_Pre - sorry it's .csv) that works with my listed code.

The general method of producing my reports in the original post, includes taking a raw data set (like MockUp_Pre) in which these variables are included (as well as several other variables not used), running a proc tabulate on them using code like this (that works with MockUp_Pre):

proc tabulate data=MockUp_Pre out=MockUp;
	class patient_type state / mlf preloadfmt order=data;
	var Outcome;
	table (patient_type = 'Patient status' all='Total'), 
          (state = '')*(Outcome=''*F=comma.  colpctn*f=5.1); /*columns*/
	format patient_type patient_type_fmt. state state_fmt.;
	keylabel sum="No." colpctn="%";
run;

/*Additional quirk we do to add 'Total' label to summary rows output in proc tabulate for patient_type*/
DATA MockUp;
	SET MockUp;
	IF patient_type = " " THEN patient_type = "Total"; /*Labelling will allow Reports below to include 'Total'*/
RUN;

The proc tabulate, then outputs the (modified) MockUp output file (attached as a .csv).

I'd then use the original PROC Report code listed in my original post to output the original report attachment.

However, now given that what I want has changed (to the embedded excel mockup(s) above), my existing code obviously isn't suitable.

Hopefully this additional information can assist. You all have greater expertise than I.

I'm happy to put in the work to create a custom Report Writing Interface (as suggested by @ballardw) as it will help me in the long term provided that what I want is actually possible at all, and not impossible with PROC REPORT and the current dataset layout I have.

Cheers,

Matt

[ballardw]

---

@Mertthew wrote:

Hi Cynthia (and Everyone),

Thanks for the quick replies. Sorry, I can't share the actual data, but will create dummy data that will work with my code below as well as a mockup of desired appearance / output. 

I understand that the nesting the dummy column under the across variable means the formatting is already set in stone and can not be altered, that was just the result of tinkering and got me closest to what I wanted at this stage, although obviously falls short.

What I want (unformatted) looks like this:

I've highlighted the column gaps I want.

The reason I want them this way, rather than bearing with the dummy column solution or adding manually them after the fact is because given the guidelines my organisation has for presentation of tables within reports, I need the gaps. Having these gaps, assists with custom underlining that I do want i.e. having gaps between the states but not the stat headings like so:

Having SAS output my tables as above, already formatted, also avoids a great deal of manual tinkering with the spacing and buffer as suggested which could lead to human error, especially since I have about 50 odd tables to produce in this (and similar) formats/styles. Unfortunately my organisation, while wanting them a certain way, does not provide resources to automate this via code.

To assist, I've created and attached a 50-observation mockup raw dataset (MockUp_Pre - sorry it's .csv) that works with my listed code.

The general method of producing my reports in the original post, includes taking a raw data set (like MockUp_Pre) in which these variables are included (as well as several other variables not used), running a proc tabulate on them using code like this (that works with MockUp_Pre):

proc tabulate data=MockUp_Pre out=MockUp;
	class patient_type state / mlf preloadfmt order=data;
	var Outcome;
	table (patient_type = 'Patient status' all='Total'), 
          (state = '')*(Outcome=''*F=comma.  colpctn*f=5.1); /*columns*/
	format patient_type patient_type_fmt. state state_fmt.;
	keylabel sum="No." colpctn="%";
run;

/*Additional quirk we do to add 'Total' label to summary rows output in proc tabulate for patient_type*/
DATA MockUp;
	SET MockUp;
	IF patient_type = " " THEN patient_type = "Total"; /*Labelling will allow Reports below to include 'Total'*/
RUN;

The proc tabulate, then outputs the (modified) MockUp output file (attached as a .csv).

I'd then use the original PROC Report code listed in my original post to output the original report attachment.

However, now given that what I want has changed (to the embedded excel mockup(s) above), my existing code obviously isn't suitable.

Hopefully this additional information can assist. You all have greater expertise than I.

I'm happy to put in the work to create a custom Report Writing Interface (as suggested by @ballardw) as it will help me in the long term provided that what I want is actually possible at all, and not impossible with PROC REPORT and the current dataset layout I have.

Cheers,

Matt

---

If you are going to use custom formats, especially with options like MLF and PRELOADFMT we need the definitions of the formats as well.

And here is an example of how to provide data. With just a text file we have to make a lot of assumptions and may not have the correct variable type. I made some guesses because things like State and Patient_type typically would not have arithmetic involved but perhaps you are using numeric values to control order in the format? We don't know.

data mockup_pre;
  infile datalines dlm=',';
  input State $ Patient_Type $ Outcome Extra_Postcode $ Extra_Age Extra_ID :$10.;
datalines;
1,1,1,123x,52,1111456Q
2,2,1,243y,33,1117832Q
3,3,1,245x,42,334876Q
4,3,1,265z,25,2188852Q
5,2,1,276y,66,9482734L
6,1,1,231z,78,5426743L
7,1,1,257x,93,3546892Q
2,1,1,814y,21,4217432L
3,2,1,629x,15,2135629Q
4,2,1,345y,19,2743683Q
1,2,1,287x,36,87245275L
5,3,1,432y,52,3455267Q
6,2,1,365x,81,2478326L
1,1,1,654y,23,11637329Q
1,3,1,786x,45,5637872Q
1,2,1,563y,76,2475632L
1,2,1,234z,28,2358326L
2,3,1,543x,37,2748394L
5,2,1,687y,42,5621847Q
3,2,1,874x,44,1736458L
5,1,1,734z,51,9564823L
7,3,1,134x,62,2756439Q
6,2,1,654z,66,3647284L
2,2,1,634x,43,27481456Q
3,1,1,123x,38,32869371Q
8,1,1,245x,29,94837267Q
1,2,1,345y,21,94738567L
2,3,1,814y,22,36748238Q
4,1,1,654y,66,2469257Q
6,2,1,786x,74,3748521L
7,2,1,234z,53,27848274Q
6,3,1,654z,35,1468932L
4,2,1,257x,46,2859362L
2,1,1,364y,74,2748294L
2,2,1,734z,72,275839Q
3,2,1,254z,82,2856732Q
8,3,1,123x,43,285374Q
2,1,1,243y,67,8592374L
3,1,1,814y,19,3212273L
5,3,1,345y,32,217583Q
6,2,1,287y,77,265843L
4,1,1,287x,37,2847362Q
4,2,1,432y,62,18372549L
2,1,1,629x,64,1738492Q
8,2,1,654y,57,127353L
1,1,1,276y,36,2184926L
2,1,1,432y,83,18940372Q
2,2,1,134x,26,37590345L
3,1,1,976z,43,2374921L
3,2,1,123x,21,27493296L
;

[ballardw]

If you need to do strange things with not-quite-constant spanning you may want to investigate the data step Report Writing Interface. That allows you to control all sorts of things with the trade off of you having to write a lot of things that Proc Report or Tabulate do behind the scenes.

Or at least provide exactly what you expect the report to look like. Showing what you don't want is only half the question. Why show the Proc Tabulate at all? If that is exactly how you want the report to look then perhaps Tabulate is the tool as dimensions are built a tad differently between the two procedures. If it is not exactly what you want then it is not adding to the discussion, just showing another example of what you don't want.

Make the table in a spread sheet manually that you do want. Then show that.

[Mertthew]

Hi ballarw,
Happy to investigate more intensive Report Writing Interface if needed if I can't get what I want out of Proc Report.

I'm apologise for not explaining the reason I listed the proc tabulate output there. That output gives me basically the same as the first set of Proc Report code. As I had it handy, I wanted to use it to illustrate what I get from the Proc Report BEFORE the addition of the dummy column code to produce the second output. I was hoping to give you an idea of where I started with the original proc report (via the approximate tabulate output) and then where I got to thus far with the modified proc report (via the report output with the dummy column).

I know also provided additional information above in response to Cynthia's reply including what I want and mockup data to go with my code. Sorry for the lack of details at the outset.
Cheers,

Matt

[Cynthia_sas · Accepted solution]

Hi:

  The value of PROC TABULATE or PROC REPORT with data such as you have (where STATE can be in the columns) is that you don't need to have a separate column for each STATE value. TABULATE and REPORT will make a column for each unique value of STATE automatically. To get a blank column where you want, you'd have to change the structure of your data.

  See the below example, using SASHELP.CLASS (which only has 2 possible values for the ACROSS variable of SEX). In this program, the #1 program shows approximately what you're getting now. The #2 program (without the dummy column) creates an OUTPUT dataset from PROC REPORT using the OUT= option, which is essentially restructuring the data file into a summarized file as shown in the PROC PRINT #3 -- in this output you can see the absolute column numbers that PROC REPORT internally uses for ACROSS column values. Then the variables are renamed for clarity and a "blank column" variable is created in a DATA step.  In the final PROC REPORT (#4), you can see that the output is close to what you wanted:

The challenge with trying to use the Report Writing Interface, is that, as shown here https://support.sas.com/rnd/base/ods/Tipsheet_RWI.pdf   is that the Reporting Writing Interface is only supported for HTML and PDF destinations.

  So you can get close to what you want with PROC REPORT but it will take restructuring your data.

Cynthia

[Mertthew]

Hi Cynthia,

Thanks for this - it's really comprehensive. 

I was afraid that I'd have to do a restructure as shown in your final options to get exactly what I want. Given the amount of specific variables and reports, it seems that I'll have to accept the dummy column solution and perhaps try to offset the spanning heading position in the heading cell.

Not elegant, but workable. 

Thanks again for bearing with me while I tried to provide what was requested in small amounts. Shame about the Report Writing Interface being limited to pdf / html for now. Hopefully that expands in future.

Cheers,

Matt.