unspan header in proc report

[Rolf24]

Hi there,

Does anybody now, how I can UNspan a header in SAS 9.4? A customer of our Sales department like to get the header above each single cell.

Here is an example with a spanned header:

proc report data=sashelp.prdsale nowd split='00'x ;
column product country, region, actual;
define product / order=data group;
define country / order=data across '';
define region / order=data across '';
define actual / analysis sum format=commax16.2 'Sum actual';
run;

I like to get the column "country" over every single cell. I already tried to get this with computed columns and variables, which works fine with rows but not with headers. 

The report now looks like this, but ...

I want to get the report like this, with not spanned headers.

Any suggestions? Thanks in advance.

[Cynthia_sas · Accepted solution]

Hi:

  I can get you closer, but you have to pump both values into 1 cell, which may not be what the customers want:

  The only other thing I can think of would be to make two passes through the data by modifying the program to use out= and create a "flattened" data set with a separate column for each unique combo of across variables. That way, you can do this (report #3):

  You could actually do the OUT= on report #1 or report #2. It doesn't matter since the absolute column numbers would be the same in either case.

Hope this gives you some ideas.

Cynthia

[Rolf24]

Hi Cynthia,

thx for your really fast response. I already tried earlier to combine the headers like in your report 2, but indeed that's not what the customer want. In my real report, the headers (cluster and company) both are numeric columns with an user-defined format, which will let you see a long text label (company names with spaces and numbers).  

And to make it "a little bit" more complicated, the column numbers are not absolute but differ. If another company with the depending cluster is added, everything should be dynamic changed in the report at this case.

As work around, I can use your static report 3 of course, but I have to change it any time, a new company is added.

Is there no other way to make it dynamic?

Thx in advance and I wish you and your team already a Happy Christmas and  a wonderful New Year!

Rolf

[ballardw]

Perhaps go back a step earlier and create a new column header variable combining the formatted values with a separation character delimiting the two values.

I have an example with combining a formatted character variable with numeric but the principal is the same with formatted numeric values.

proc format library=work;
value $fm
'F' = 'Female'
'M' = 'Male'
;

data work.class;
   set sashelp.class;
   newcol = catx('*',put(sex,$fm.),age);
run;

/* OLD Report code*/
proc report data=work.class;
   column name sex,age,weight;
   define name /order group;
   define sex /order across ' ';
   define age /order across ' ';
   define weight/ analysis mean 'Mean';
run;

/* new report*/
proc report data=work.class split='*';
   column name newcol,weight;
   define name /order group;
   define newcol /order across ' ';
   define weight/ analysis mean 'Mean';
run;

With two or more numeric values you would have catx use the put(var,fmt.) for each value.

I'm not found of adding a character value to a string for split that isn't visible in the data so I used * instead of '00'x.

This CATX approach could be used with multiple variables to stack into a single cell.

[Cynthia_sas]

Hi:
I'm sorry, you asked your question about SASHELP.PRDSALE and my answer was tailored, therefore to character variables. I assume that it would be possible, to do what you want, My suspicion is that it will take multiple passes through the data. However, without data that is more like what you actually have, I really can't provide a better answer.
The only way to make code more "dynamic" is to macroize it. But in order to macroize a program like this, you have to start with a working program.

When you use ACROSS items in PROC REPORT, the ACROSS items get spanning headers. The only way to "unspan" the headers is to "flatten" the data structure, so that every unique combo of ACROSS variables bcomes a separate column in the data from the first pass. Then, that new dataset, essentially "flattened" can have headers assigned as I show in my program #3.

If the variables are numeric, that only complicates things a bit. But I believe you'll still need 2 passes and SAS Macro in the long run.

Cynthia

[Rolf24]

The solution of Cynthia is more what I'm looking for.

@ballardw: As I already mentioned, my customer don't want to combine the cluster with the companies in one cell. But indeed, it's a nice solution to make long column names with more then 32 chars visible. 

Thank you both for your response!