topic Re: How to count the number of occurrences in each percentile group in SAS Programming

How to count the number of occurrences in each percentile group

nicksampson22 — Tue, 23 Aug 2022 18:44:03 GMT

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!!!!

Re: How to count the number of occurrences in each percentile group

PaigeMiller — Tue, 23 Aug 2022 20:40:38 GMT

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;

Re: How to count the number of occurrences in each percentile group

nicksampson22 — Wed, 24 Aug 2022 15:47:28 GMT

This worked! Thank you so much!