Send As SMS

Saturday, May 05, 2007

Mixing Means and PctNs in a column in PROC REPORT

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.

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;

Here is the 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

0 Comments:

Post a Comment

<< Home