Sas query to get highest 3 rows for date

[Sirisha1520]

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

[PaigeMiller]

---

@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 ?

[ballardw]

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)

[PaigeMiller]

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.

[Sirisha1520]

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

[ballardw]

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

Example starting data helps.

Example output for that input helps more.

[Sirisha1520]

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

[PaigeMiller]

---

@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?

[PaigeMiller]

---

@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?

[Sirisha1520]

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

[Kurt_Bremser]

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?

[Sirisha1520]

Yessss

[Kurt_Bremser]

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;