How to count the number of occurrences in each percentile group

[nicksampson22]

Hello all,

I am trying to find how many observations there are in each percentile group.

The dataset looks like:          (there are many records for each individual drug. Each row is an order. Table is grouped by drug family and basecode. Total sum is amount of drug units per order)

      Drug Family     Base_Code      Total_Sum

1    Drug1                1111                  500

2    Drug1                1111                 4100

3    Drug2                1233                2500

4    Drug3                2243                700

5    Drug3                2243                1250

I calculated the percentiles using the code below:

proc sort data=dataset1 out=dataset2; by Drug_Family; run;

Proc Univariate Data=dataset1 noprint;

          by Drug_Family Base_Code notsorted ;

          var TotalSum;

Output Out=NewData Pct1Pre=Units_Percentile Pct1Pts=50,55,60,65,70,75,80,85,90,95;

My current percentile table looks like this: 

Drug Family     Drug code           Units_Percentile50         Units_Percentile55   ......     Units_Percentile95

Drug 1               1111                   500                                      540                                           2500

Drug 2               1112                   240                                     260                                           1200

The table I am trying to create I want to look like this:

Drug Family     Drug code           N_Percentile50         N_Percentile55   ......     N_Percentile95

Drug 1               1111                   60 (observations)       52                                   70

Drug 2               1112                   20                               30                                   35

I am really struggling to create this so any help would be much appreciated. Thanks!!!!

[PaigeMiller · Accepted solution]

Use PROC RANK with the option GROUPS=20. This will put your data into 20 groups, or groups of width 5 percentile points (so group 0 will contain 0-5 percentile and group 19 will contain 95-100 percentile). Then a simple PROC FREQ will get you the count.

proc rank data=have out=ranks groups=20;
    by drug_family base_code;
    var total_sum;
    ranks total_sum_rank;
run;
proc freq data=ranks;
    table total_sum_rank;
run;
    
    

[nicksampson22]

This worked! Thank you so much!