translating SAS data steps to SQL

[MadAboutCoffee]

hello,

i hope someone could help me with my query.
I already have a running code for this but is trying to convert it to become a volatile code/table.
However, I am not so sure if data set can be used for volatile.
Currently, this is the code that I use to get the last record of a specific ID based on the 'Acct_mod_dt' (Account modification Date).

1st is to sort the data set by ID and acct_mod_dt:

proc sort data=total_member_base out=total_member_base_sort;
by id acct_mod_dt;


2nd is to just leave all the IDs which are not 'N' in status and then get the latest entry based on the modification date:

data max_eff_from_dt;
set total_member_base_sort;
by id acct_mod_dt;
if last.id;

if status ne 'Y' then delete;
run;


I have attached an Excel file with simple illustration of what the outcome should be.

Thank you soooo much!

 

[MadAboutCoffee]

Sorry I think I'm having issues attaching a file to my recently posted thread. I have just copied and paste what is showing on my attachment to this thread instead.

INITIAL DATA SET:

ID MEMBERSHIP status ACCT_MOD_DT
209714593 CLUB Y 8-Apr-07
189616270 GOLD Y 22-Jan-10
240609905 PLTN Y 18-Jun-06
209714593 CLUB Y 21-Jan-10
240609905 CLUB N 25-Dec-09
240609905 PLTN N 1-Jan-00
189616270 GOLD Y 27-Apr-07
240609905 CLUB N 7-Jan-09
189616270 GOLD Y 1-Jan-00
240609905 GOLD N 8-Jan-08
209714593 CLUB N 8-Apr-07
240609905 CLUB N 25-Dec-09
168015884 GOLD Y 1-Jan-00
168015884 GOLD Y 30-Dec-09
168015884 GOLD Y 21-Dec-09
240609905 PLTN Y 6-Aug-07
240609905 CLUB Y 19-Dec-09
209714593 CLUB N 1-Jan-00
189616270 GOLD Y 27-Apr-07
168015884 GOLD N 30-Dec-09

AFTER SORTING:

ID MEMBERSHIP status ACCT_MOD_DT
168015884 GOLD Y 1-Jan-00
168015884 GOLD Y 21-Dec-09
168015884 GOLD Y 30-Dec-09
168015884 GOLD N 30-Dec-09
189616270 GOLD Y 1-Jan-00
189616270 GOLD Y 27-Apr-07
189616270 GOLD Y 27-Apr-07
189616270 GOLD Y 22-Jan-10
209714593 CLUB N 1-Jan-00
209714593 CLUB Y 8-Apr-07
209714593 CLUB N 8-Apr-07
209714593 CLUB Y 21-Jan-10
240609905 PLTN N 1-Jan-00
240609905 PLTN Y 18-Jun-06
240609905 PLTN Y 6-Aug-07
240609905 GOLD N 8-Jan-08
240609905 CLUB N 7-Jan-09
240609905 CLUB Y 19-Dec-09
240609905 CLUB N 25-Dec-09
240609905 CLUB N 25-Dec-09
*ALL those with the Status of N should be dropped and those with Y will only be the ones to be counted towards the Final Data Set.

FINAL DATA SET:

ID MEMBERSHIP status ACCT_MOD_DT
168015884 GOLD Y 30-Dec-09
189616270 GOLD Y 22-Jan-10
209714593 CLUB Y 21-Jan-10
240609905 CLUB Y 19-Dec-09