CODEWITHSUNDEEP
sqlsas
Angelo Lapa Huamani
Angelo Lapa Huamani

Reputation: 53

How to identify records with a specific date in SAS?

I am new to SAS, and I have a problem with identifying records in a table with a specific date. I have declared a variable called fec_diario.

%let fec_diario = 20210423;

I have the RATING table:

cod const value date
1 11 321 20210413
2 22 543 20210423
3 33 222 20210413
4 44 111 20210413
4 44 555 20210423
6 66 666 20210423
7 77 216 20210520
8 88 766 20210520

And I need to make a query that shows me the clients that have the most recent date, that is, a date less than or equal to the one declared in the variable fec_diario.

I achieved this by executing the following code:

proc sql;
create table rating_process as
select * from rating
where date <= &fec_diario;
run;

And I got this result:

cod const value date
1 11 321 20210413
2 22 543 20210423
3 33 222 20210413
4 44 111 20210413
4 44 555 20210423
6 66 666 20210423

However, if I have more than one record with the same code, I need it to show me only the record with the most recent date..

In conclusion, i should get a table like this:

cod const value date
1 11 321 20210413
2 22 543 20210423
3 33 222 20210413
4 44 555 20210423
6 66 666 20210423

It would be very helpful if someone could suggest a solution to my case, thank you very much.

Upvotes: 0

Views: 883

Answers (1)

Reeza
Reeza

Reputation: 21264

Try this, it may work in SAS but may not in other languages:

proc sql;
create table rating_process as
select * from rating
where date <= &fec_diario
group by cod
having date = max(date);
quit;

Or use a PROC SORT and LAST logic.

Sort so that the largest date is the last record per COD.

proc sort data=rating_process; by COD DATE; run;

Now take the last record per COD.

data want;
   set rating_process;
   by COD DATE;
   if last.COD;
run;

Note that SQL requires a QUIT, not a RUN.

Upvotes: 1

Related Questions