topic Re: How to report a table with grouped variables? in ODS and Base Reporting

How to report a table with grouped variables?

guibuzi — Thu, 01 Feb 2018 09:01:40 GMT

I encountered some problems when generating a report table.

I want generating a table in such form (left). But I can only get the form on the right.

Here are my codes:

data tmp;
  input num var $ value group count f $ p $;
cards;
1 LMRN 0 1 0 NA NA 
1 LMRN 0 2 0 NA NA 
1 LMRN 1 1 72 NA NA 
1 LMRN 1 2 72 NA NA 
2 LMLN 0 1 0 - 1.0000 
2 LMLN 0 2 1 - 1.0000 
2 LMLN 1 1 72 - 1.0000 
2 LMLN 1 2 71 - 1.0000 
3 YYRN 0 1 1 0.6220 0.6220 
3 YYRN 0 2 3 0.6220 0.6220 
3 YYRN 1 1 70 0.6220 0.6220 
3 YYRN 1 2 69 0.6220 0.6220 
4 YYLN 0 1 2 1.0000 1.0000 
4 YYLN 0 2 2 1.0000 1.0000 
4 YYLN 1 1 69 1.0000 1.0000 
4 YYLN 1 2 70 1.0000 1.0000 
5 YZRN 0 1 61 0.4601 0.4601 
5 YZRN 0 2 64 0.4601 0.4601 
5 YZRN 1 1 11 0.4601 0.4601 
5 YZRN 1 2 8 0.4601 0.4601 
6 YZLN 0 1 58 1.0000 1.0000 
6 YZLN 0 2 58 1.0000 1.0000 
6 YZLN 1 1 14 1.0000 1.0000 
6 YZLN 1 2 14 1.0000 1.0000 
7 HMRN 0 1 0 NA NA 
7 HMRN 0 2 0 NA NA 
7 HMRN 1 1 72 NA NA 
7 HMRN 1 2 72 NA NA 
8 HMLN 0 1 0 NA NA 
8 HMLN 0 2 0 NA NA 
8 HMLN 1 1 72 NA NA 
8 HMLN 1 2 72 NA NA 
;
run;

proc format;
  value group_label 1="Group1"
                    2="Group2";
  value value_label 0="N"
                    1="F";
run;

proc report data=tmp split="|" spanrows style(column)={just=center};
  column var value (count,group f p n);
  define var/group order=data;
  define value/group descending format=value_label.;
  define group/across order=internal format=group_label. '';
  define count/"";
  define n/noprint;
  define f/group 'F' style(column)={vjust=m};
  define p/group 'P value' style(column)={vjust=m};
run;

Re: How to report a table with grouped variables?

Ksharp — Thu, 01 Feb 2018 13:55:24 GMT

Use ORDER usage not GROUP. and you maybe need to change your data structure.

proc report data=tmp split="|" nowd spanrows style(column)={just=center};
  column var value count,group  n f p;
  define var/order order=data;
  define value/order descending format=value_label.;
  define group/across order=internal format=group_label. '';
  define count/"";
  define n/noprint;
  define f/order 'F' style(column)={vjust=m};
  define p/order 'P value' style(column)={vjust=m};
run;

Re: How to report a table with grouped variables?

ballardw — Thu, 01 Feb 2018 16:10:17 GMT

Please describe the actual concern? Is it that the cells of like values are not merged?

Also show your code (at least). Example data might help.

Re: How to report a table with grouped variables?

guibuzi — Thu, 01 Feb 2018 16:36:31 GMT

I'm sorry for my English. 'var'，'value' are grouped variables. I want to generate a report similar to the picture on the left, but I don't want repeat the value of the variable (such as 'f' 'p') from one row to the next if the value does not change.

Example data and my code already posted.

Re: How to report a table with grouped variables?

guibuzi — Thu, 01 Feb 2018 16:40:26 GMT

Thank you for your help. I have tried your method, but the report has some problems.

Re: How to report a table with grouped variables?

Cynthia_sas — Fri, 02 Feb 2018 02:19:45 GMT

HI:

PROC REPORT has limitations and one of the limitations you have discovered. Since the combination of VAR and VALUE produces 2 distinct rows, it is hard to get what you want with the rows in the middle collapsed and the rows on the left and right side of the table to be spanning when you use ACROSS for the GROUP column.

However, you can achieve this type of report with two passes through the data. The first pass is the PROC REPORT step you have to get ALMOST what you want into a DATA set.You can use OUT= to get the dataset you ALMOST want. Here's the PROC REPORT output from the first pass thru the data:

This first step will collapse the rows and essentially transpose GROUP 1 and GROUP 2. This dataset, WORK.ALMOST will look like this:

Then, you can send the WORK.ALMOST data set to PROC REPORT, where the the COLUMN statement will be slightly different and there is no ACROSS usage on the report. Also, I used the NUM variable on both reports to control the order of the VAR column, producing this:

Here's the complete code (uses the same data that was posted previously):


proc format;
  value group_label 1="Group1"
                    2="Group2";
  value value_label 0="N"
                    1="F";
run;
  
** use REPORT to "transpose" data into desired structure;
** include NUM in report for ordering purposes;
** not sure why N is even needed;
proc report data=tmp split="|" spanrows 
     out=work.almost(rename=(_c4_=Group1 _c5_=Group2) drop=_break_)
     style(column)={just=center};
title '1) Original report is ALMOST what is wanted';
title2 'Need to restructure data for RWI';
  column num var value  count,group  f p;
  define num / group noprint;
  define var/group order=data;
  define value/group descending format=value_label.;
  define group/across order=internal format=group_label. '';
  define count/" ";
  define f/group 'F' style(column)={vjust=m};
  define p/group 'P value' style(column)={vjust=m};
run;
  
proc print data=work.almost;
  title 'data set after first pass -- used proc report to transpose and collapse rows';
format value value_label.;
run;
  
proc sort data=work.almost; by num var value;
run;

proc report data=almost spanrows;
  title '2a) Using Transposed and Summarized data with PROC REPORT';
  column num var value group1 group2 f p;
  define num / order noprint;
  define var / order 'Var';
  define value / display 'Value' f=value_label.;
  define group1 / display 'Group 1';
  define group2 / display 'Group 2';
  define f / order 'F';
  define p / order 'P Value';
run;

There's also a solution using the Report Writing Interface, but it would require more logic to control and test for true duplicates in the F and P columns (which is something that the PROC REPORT ORDER usage will do for you automatically).

cynthia

Re: How to report a table with grouped variables?

guibuzi — Fri, 02 Feb 2018 02:47:09 GMT

Thanks for your help.