Date:         Thu, 14 Feb 2008 15:32:41 -0600
Reply-To:     bruce johnson <chimanbj@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         bruce johnson <chimanbj@GMAIL.COM>
Subject:      Proc Rank: Filling in missing ranks
Content-Type: text/plain; charset=ISO-8859-1

Hello geniuses!

I have inherited some code that calculates percentiles using Proc
Rank.  But it unfortunately forces me to have to manually calculate
some of the percentiles when the sample size is low.  I want to figure
out a way to do it programatically.

I have a dataset that tracks attendance for various teams over the
course of 12 months.  The number of teams that actually submit the
attendance information varies from month to month.  When the month's
sample gets low enough, some of the percentiles are not output (I'm
only looking at ranks of 10, 25 and 75).  So, for the ones that are
missing, I need to find the adjacent percentiles and average them to
get the rank for the missing percentile.

Month  Attendance  Attendance_Rank
1          100                9
1          104                11

In that instance, I would average the Attendance_Rank of 9 and 11 to
get the calculated Attendance_Rank of 10 (attendance: 102).  I don't
want to do it manually.  Is there a way to do it in the program?  I
have provided code that will simulate the data that I'm working with.

Thanks...


data attendance;
 do j=1 to 12;
  month=j;
   do i=1 to ranuni(0)*200;
   team=i;
   attendance=floor(ranuni(0)*1000000);
   output;
  end;
 end;
 drop i j;
run;
proc sort data=attendance;
 by month;
run;
proc rank data=attendance out=attendance_ranked
          groups=100 ties=high;
                  by month;
                  ranks attendance_index;
                  var attendance;
run;
proc sort data=attendance_ranked;
 by month attendance_index;
run;
title "Identify Missing Percentiles";
run;
proc freq data=attendance_ranked(where=(attendance_index in (10,25,75)));
 tables month*attendance_index / missing sparse nocum norow nocol nopct;
run;