As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Mixing Means and PctNs in a column in PROC REPORT

From sasCommunity
Jump to: navigation, search

Problem Statement

In a posting to SAS-L, Janina Nowles asked how to create a report where a column contains PctN at the detail level but the Mean at the summary level. The code below comes pretty close; the data required some preprocessing to make it suitable.

SAS Code

dm 'clear log; clear out;';
 
proc format; 
   value ldfmt 
      9 ='Like Extremely' 8 ='Like very much' 
      7 ='Like moderately' 6 ='Like slightly' 
      5 ='Neither like nor dislike' 4 ='Dislike slightly' 
      3 ='Dislike moderately' 2 ='Dislike very much' 
      1 ='Dislike extremely'
      ;
run;
 
data raw; 
   infile cards; 
   input id sample q1 q2; 
   label q1='constr' q2='extrus'; 
cards; 
1 558 6 8 
1 911 5 6 
2 911 8 8 
2 558 7 7 
3 558 7 8 
3 911 8 8 
4 911 7 7 
4 558 7 7 
5 558 7 6 
5 911 8 9 
6 911 6 8 
6 558 6 8
;;;; 
 
data normal;
   set raw;
   array qnames{2} $ _temporary_ ('q1' 'q2');
   array qvals{2} q1 q2;
   do i = 1 to 2;
      q = qnames{i};
      value = qvals{i};
      select (sample);
         when (558) sample_558 = value;
         when (911) sample_911 = value;
         otherwise  error 'Unknown sample type';
      end;
      output;
   end;
   drop q1 q2 i;
run;
 
proc report data=normal missing nowindows nocenter split='!';
 
   column q value ('- Sample -' 
                   sample_558 sample_558=sample_558_n show_558 
                   sample_911 sample_911=sample_911_n show_911);
 
   define q / group;
   define value        / group format=ldfmt. order=internal descending;
   define sample_558   / analysis mean format=10.0 '558 Mean' noprint;
   define sample_911   / analysis mean format=10.0 '911 Mean' noprint;
   define sample_558_n / analysis n format=10.1 '558 N' noprint;
   define sample_911_n / analysis n format=10.1 '911 N' noprint;
   define show_558     / computed format=10.0 '558!%';
   define show_911     / computed format=10.0 '911!%';
 
   compute before q;
      n_558 = sample_558_n;
      n_911 = sample_911_n;
   endcomp;
 
   break after q / summarize skip dol;
 
   compute show_558;
      if _break_ = ' ' then 
         do;
         if min(sample_558_n, n_558) = 0 then 
            show_558 = .;
         else 
            show_558 = 100 * sample_558_n / n_558;         
         end;
      else 
         do;
         show_558 = sample_558.mean;
         call define(_col_, 'format', '10.1');
         end;
   endcomp;
 
   compute show_911;
      if _break_ = ' ' then 
         do;
         if min(sample_911_n, n_911) = 0 then 
            show_911 = .;
         else 
            show_911 = 100 * sample_911_n / n_911;         
         end;
      else 
         do;
         show_911 = sample_911.mean;
         call define(_col_, 'format', '10.1');
         end;
   endcomp;
 
   compute after q;
      q = 'Mean';
   endcomp;
 
run;

SAS Output

                                     ------- Sample -------
                                            558         911
 q                            value           %           %
 q1        Like very much                     .          50
           Like moderately                   67          17
           Like slightly                     33          17
           Neither like nor dislike           .          17
 ========                            ==========  ==========
 Mean                                       6.7         7.0
 q2        Like Extremely                     .          17
           Like very much                    50          50
           Like moderately                   33          17
           Like slightly                     17          17
 ========                            ==========  ==========
 Mean                                       7.3         7.7

Discussion

Please use the discussion tab to comment on or ask questions about this example.

Summary

This entry was created by Donh as an example for Jack Hamilton using an example that he posted on his code blog.