The SAS Output Delivery System and reporting techniques

How to report a table with grouped variables?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How to report a table with grouped variables?

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.

snipaste_1.pngsnipaste_2.png

 

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;

 


Accepted Solutions
Solution
‎02-01-2018 09:44 PM
SAS Super FREQ
Posts: 9,434

Re: How to report a table with grouped variables?

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:

after_first_pass.png

 

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

work_almost.png

 

  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:

report_2a.png

 

  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

 

View solution in original post


All Replies
Super User
Posts: 10,850

Re: How to report a table with grouped variables?

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;

New Contributor
Posts: 4

Re: How to report a table with grouped variables?

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

snipaste_3.png

Super User
Posts: 13,942

Re: How to report a table with grouped variables?

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.

New Contributor
Posts: 4

Re: How to report a table with grouped variables?

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.

 
Solution
‎02-01-2018 09:44 PM
SAS Super FREQ
Posts: 9,434

Re: How to report a table with grouped variables?

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:

after_first_pass.png

 

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

work_almost.png

 

  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:

report_2a.png

 

  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

 

New Contributor
Posts: 4

Re: How to report a table with grouped variables?

Posted in reply to Cynthia_sas
Thanks for your help.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 238 views
  • 1 like
  • 4 in conversation