topic Re: Sas query to get highest 3 rows for date in SAS Programming

Sas query to get highest 3 rows for date

Sirisha1520 — Wed, 15 Sep 2021 18:36:53 GMT

I have a requirement to get highest 3 rows for respective policy and effective date. Key is policy and effective date. am trying using group by clause having count(*) <=3, but its avoiding rows having more than 3 in input file. Please suggest how to write query

Re: Sas query to get highest 3 rows for date

PaigeMiller — Wed, 15 Sep 2021 19:03:14 GMT

@Sirisha1520 wrote:
am trying using group by clause having count(*) <=3, but its avoiding rows having more than 3 in input file.

Shouldn't that be count(*)>=3 ?

Re: Sas query to get highest 3 rows for date

ballardw — Wed, 15 Sep 2021 19:04:28 GMT

Since "highest 3", assuming you mean largest or greatest, is in effect an order statistic Proc SQL is likely not the best approach in general.

Proc means documentation has exactly an example of finding the three largest values by grouping variable:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/n0afy7c5m16m9dn1s60z9krjmwxq.htm

The key parts are the Idgroup, max (statistic to select with) and out= (number of greatest values to select)

Re: Sas query to get highest 3 rows for date

PaigeMiller — Wed, 15 Sep 2021 19:44:24 GMT

PROC RANK is probably the best way to do this, as it gives you options on how to handle things in the case where there are ties.

Re: Sas query to get highest 3 rows for date

Sirisha1520 — Wed, 15 Sep 2021 20:37:42 GMT

I tried rank but that was giving 1.2, 2.5 , 4.5… so again causing duplicates as i have multple rows for policy with same effective date. Also i have sorted the file for descending order in date due to which taking count less than 3. I need first 3 records for policy with different date. Example below:

0120210101 B
0120210101 A
0120210201 C
0120210301 C

O/p expected:

0120210101 B
0120210201 C
0120210301 C

Re: Sas query to get highest 3 rows for date

ballardw — Wed, 15 Sep 2021 20:48:22 GMT

Don't see any obvious date so "first 3" looks meaningless.

Example starting data helps.

Example output for that input helps more.

Re: Sas query to get highest 3 rows for date

PaigeMiller — Wed, 15 Sep 2021 21:06:55 GMT

@Sirisha1520 wrote:
I tried rank but that was giving 1.2, 2.5 , 4.5… so again causing duplicates as i have multple rows for policy with same effective date. Also i have sorted the file for descending order in date due to which taking count less than 3. I need first 3 records for policy with different date. Example below:

0120210101 B
0120210101 A
0120210201 C
0120210301 C

O/p expected:

0120210101 B
0120210201 C
0120210301 C

Yes, you have ties in your data, and there are many options in PROC RANK on how to handle ties, most likely one of them will be to your liking.

Also, please don't post unintelligible dates like 0120210101 without an explanation. What is this?

Re: Sas query to get highest 3 rows for date

Sirisha1520 — Thu, 16 Sep 2021 03:34:29 GMT

Policy Date Plan
01 20210101 B
01 20210101 A
01 20210201 C
01 20210301 C

O/p expected:

01 20210101 B
01 20210201 C
01 20210301 C

Re: Sas query to get highest 3 rows for date

Sirisha1520 — Thu, 16 Sep 2021 03:37:00 GMT

I was trying to explain this.

Policy Date Plan
01 20210101 B
01 20210101 A
01 20210201 C
01 20210301 C

O/p expected:

01 20210101 B
01 20210201 C
01 20210301 C

Re: Sas query to get highest 3 rows for date

Kurt_Bremser — Thu, 16 Sep 2021 06:32:44 GMT

Let me help you formulate your question:

First, you want to have only max of Plan for any single date within a Policy.

Next, you want the earliest three dates for any given Policy.

Is that correct?

Re: Sas query to get highest 3 rows for date

Sirisha1520 — Thu, 16 Sep 2021 08:18:34 GMT

Yessss

Re: Sas query to get highest 3 rows for date

Kurt_Bremser — Thu, 16 Sep 2021 08:26:35 GMT

Then run this:

data have;
input Policy :$2. Date :yymmdd8. Plan :$1.;
format date yymmdd10.;
datalines;
01 20210101 B
01 20210101 A
01 20210201 C
01 20210301 C
;

proc sort data=have;
by policy date plan;
run;

data want;
set have;
by policy date;
if first.policy then count = 0;
if last.date;
count + 1; /* sum statement implies retain */
if count le 3;
drop count;
run;

Re: Sas query to get highest 3 rows for date

PaigeMiller — Thu, 16 Sep 2021 11:32:34 GMT

@Sirisha1520 wrote:
Policy Date Plan
01 20210101 B
01 20210101 A
01 20210201 C
01 20210301 C

O/p expected:

01 20210101 B
01 20210201 C
01 20210301 C

This does not explain the dates. Are they YYYYMMDD or YYYYDDMM?