Proc report: transpose select rows to columns?

[Cruise]

Hi folks and @ballardw ,

I'm trying to convert DATA HAVE to a DESIRED OUTPUT as shown in the image below.

The problem was solved for slightly different data in the post following:

https://communities.sas.com/t5/SAS-Programming/Select-certain-rows-to-transpose-for-a-column/m-p/539...

Below is the new data.

DATA HAVE; LENGTH PARAMETER $10.;
INPUT PARAMETER $ SITES $ Level1 RR LCL UCL;
CARDS;
PM25 LIVER . 1.02 1.01 1.02
SMOKE_CAT LIVER 2 0.86 0.79 0.93
SMOKE_CAT LIVER 3 1.21 1.08 1.34
SMOKE_CAT LIVER 4 1.32 1.10 1.54
SMOKE_CAT LIVER 5 1.25 1.07 1.43
NO2 LIVER . 1.41 1.14 1.68
SMOKE_CAT LIVER 2 0.81 0.63 0.99
SMOKE_CAT LIVER 3 1.60 1.23 1.97
SMOKE_CAT LIVER 4 0.59 0.63 0.54
SMOKE_CAT LIVER 5 0.98 0.96 0.99
PM25 STOMACH . 1.88 1.35 2.40
SMOKE_CAT STOMACH 2 0.34 0.23 0.45
SMOKE_CAT STOMACH 3 2.06 1.44 2.68
SMOKE_CAT STOMACH 4 0.98 0.96 0.99
SMOKE_CAT STOMACH 5 2.25 1.53 2.97
NO2 STOMACH . 2.34 1.57 3.11
SMOKE_CAT STOMACH 2 2.43 1.61 3.25
SMOKE_CAT STOMACH 3 0.58 0.56 0.59
SMOKE_CAT STOMACH 4 2.62 1.70 3.54
SMOKE_CAT STOMACH 5 2.71 1.74 3.68
;
PROC PRINT; RUN; 

[Cruise]

Hi folks and @ballardw ,

I'm trying to convert DATA HAVE to a DESIRED OUTPUT as shown in the image below.

The problem was solved for slightly different data in the post following:

https://communities.sas.com/t5/SAS-Programming/Select-certain-rows-to-transpose-for-a-column/m-p/539...

Below is the new data.

DATA HAVE; LENGTH PARAMETER $10.;
INPUT PARAMETER $ SITES $ Level1 RR LCL UCL;
CARDS;
PM25 LIVER . 1.02 1.01 1.02
SMOKE_CAT LIVER 2 0.86 0.79 0.93
SMOKE_CAT LIVER 3 1.21 1.08 1.34
SMOKE_CAT LIVER 4 1.32 1.10 1.54
SMOKE_CAT LIVER 5 1.25 1.07 1.43
NO2 LIVER . 1.41 1.14 1.68
SMOKE_CAT LIVER 2 0.81 0.63 0.99
SMOKE_CAT LIVER 3 1.60 1.23 1.97
SMOKE_CAT LIVER 4 0.59 0.63 0.54
SMOKE_CAT LIVER 5 0.98 0.96 0.99
PM25 STOMACH . 1.88 1.35 2.40
SMOKE_CAT STOMACH 2 0.34 0.23 0.45
SMOKE_CAT STOMACH 3 2.06 1.44 2.68
SMOKE_CAT STOMACH 4 0.98 0.96 0.99
SMOKE_CAT STOMACH 5 2.25 1.53 2.97
NO2 STOMACH . 2.34 1.57 3.11
SMOKE_CAT STOMACH 2 2.43 1.61 3.25
SMOKE_CAT STOMACH 3 0.58 0.56 0.59
SMOKE_CAT STOMACH 4 2.62 1.70 3.54
SMOKE_CAT STOMACH 5 2.71 1.74 3.68
;
PROC PRINT; RUN; 

[ballardw]

I'm not quite sure what you want for output.

[Cruise]

Thanks. Exact same as before. The difference is smoke_cat with 4-levels instead NY with one level in previous example. I can post screenshot using Excel in 10 min.

[Cruise]

Hi @ballardw ,

The image shows desired output. I can make painting cells based on the statistical significance a separate post. 

[ballardw · Accepted solution]

Basically the same approach: make the col and row variables needed conditionally and retain the column values for grouping the columns.

DATA HAVE; 
   LENGTH PARAMETER $10.;
   INPUT PARAMETER $ SITES $ Level1 RR LCL UCL;
   length col $ 5 row $ 20;
   retain col ;
   If parameter in ('PM25' 'NO2') then col=parameter;
   if level1=. then row='Adjusted RR';
   else row= catx(' ','Smoking Level',level1);
CARDS;
PM25 LIVER . 1.02 1.01 1.02
SMOKE_CAT LIVER 2 0.86 0.79 0.93
SMOKE_CAT LIVER 3 1.21 1.08 1.34
SMOKE_CAT LIVER 4 1.32 1.10 1.54
SMOKE_CAT LIVER 5 1.25 1.07 1.43
NO2 LIVER . 1.41 1.14 1.68
SMOKE_CAT LIVER 2 0.81 0.63 0.99
SMOKE_CAT LIVER 3 1.60 1.23 1.97
SMOKE_CAT LIVER 4 0.59 0.63 0.54
SMOKE_CAT LIVER 5 0.98 0.96 0.99
PM25 STOMACH . 1.88 1.35 2.40
SMOKE_CAT STOMACH 2 0.34 0.23 0.45
SMOKE_CAT STOMACH 3 2.06 1.44 2.68
SMOKE_CAT STOMACH 4 0.98 0.96 0.99
SMOKE_CAT STOMACH 5 2.25 1.53 2.97
NO2 STOMACH . 2.34 1.57 3.11
SMOKE_CAT STOMACH 2 2.43 1.61 3.25
SMOKE_CAT STOMACH 3 0.58 0.56 0.59
SMOKE_CAT STOMACH 4 2.62 1.70 3.54
SMOKE_CAT STOMACH 5 2.71 1.74 3.68
;
run;

proc report data=have;
   column sites row col,(rr lcl ucl);
   define sites  /group;
   define row /group;
   define col /across order=data "";
run;
   

The order=data for COL is to preserve the order from the input data set, otherwise the output defaults to formatted (alphabetical) and the "" suppresses the col value name appearing as an overall label.

[Cruise]

WOW. This is not a minor change from the previous programming to me. Glad I asked for your help! Thanks a lot. I will post the question as to paint the cells to red vs green based on the statistical significance. Can I post your solution provided here and tag you on that post following?

[ballardw]

The basic ideas for report is to ensure that each value you want to appear in a column has the appropriate value in a column indicating variable. RETAIN is one way to keep a value across records that are related to use as such. Similar is to have the correct row value. In this case I used literal text as that is fairly clear. Then the values get displayed in the intersections.

Proc tabulate is similar but the body requires requesting a statistic and suppressing the label to get similar output.

Tabulate may have advantages if making multiple tables from a single data set as you can have multiple TABLE statements as long as variables maintain the same role: Class (categorical and row or column heading) or Var numeric for summarizations.

The two procedures are intended for slightly different purposes with Report being more on the account ledger appearance and allowing calculations referencing other cells and tabulate, IMHO, better when nesting categories in multiple layers both row and column.