Fetching top 20 rows with maximum amount paid monthly through SQL

Brad39 — Wed, 18 May 2022 16:23:13 GMT

Please someone could help with below scenario to code through Proc SQL:

Scenario: we have a dataset with column : Paitent_Id, Name, Appointment_date and Amount_Paid.

I need to fetch report for top 10 Patient for each_month who paid the maximum Bill.

Below is the dummy data:

Patient_Id Patient_Name Appointment_date Bill_Paid

1 A 01/01/2000 250

2 B 21/02/2000 350

3 C 15/01/2000 400

4 D 17/02/2000 430

5 E 01/03/2000 500

Following is the code i've tried but i want to replicate same scenario through proc sql or if any other optimized solution someone could suggest, will be appreciated.

Code:

format date yymmn6.;

proc sort data=dummy out=sorted_op;

by date descending bill_paid;

run;

data final;

set sorted_op;

by date;

if first.date then n=1;

else n+1;

if n<=10;

run;

Re: Fetching top 20 rows with maximum amount paid monthly through SQL

SASKiwi — Thu, 19 May 2022 00:53:13 GMT

PROC RANK is a good way of doing this.

topic Re: Fetching top 20 rows with maximum amount paid monthly through SQL in New SAS User

Fetching top 20 rows with maximum amount paid monthly through SQL

Re: Fetching top 20 rows with maximum amount paid monthly through SQL