Alex Alex - 1 month ago 5
SQL Question

Applying operation to unique groups of ids in a table in SAS?

I have a table of values for example below. I would like to get the most recent rows of each loan id. I can get the most recent values by using the (OBS=2) function, but can't group the data to apply each operation.

I've searched online and can't do do loops in proc sql. I also can't use groupby because that just gives me summary statistics. If anyone could recommend a method to try, and even shwo me some pseudocode of how to approach the problem would be great.

loan_id Date princ_amt del_status
1 1012016 70 0
1 2012016 60 7
1 3012016 50 9
1 4012016 40 9
1 5012016 30 7
1 6012016 20 7
1 7012016 10 1
1 8012016 0 0
1 9012016 0 0
1 10012016 0 0
1 11012016 0 0
1 12012016 0 0
2 1012016 70 0
2 2012016 60 0
2 3012016 50 0
2 4012016 40 1
2 5012016 30 7
2 6012016 20 7
2 7012016 10 8
2 8012016 0 0
2 9012016 0 0
2 10012016 0 0
2 11012016 0 0
2 12012016 0 0

Answer

If this is SAS, use a Data Step with a by group.

data want;
set have;
by loan_id;
retain cnt 0;
if first.loan_id then
   cnt = 0;

cnt = cnt + 1;

if cnt <=2 then output;

drop cnt;
run;

We loop through the data and count the number of observation by loan_id. Use retain to keep the value of cnt between observations. If the count is less than or equal to 2 then we output the record.