Hi All,
I have the follwoing table but require to retain the latest_advance until it changes.
Got
Reporting_Date Account_ID Advance Latest_Advance
Jan-05 1111 0 .
Feb-05 1111 0 .
Mar-05 1111 1 Mar-05
Apr-05 1111 1 .
May-05 1111 1 .
Jun-05 1111 1 .
Jul-05 1111 2 Jul-05
Aug-05 1111 2 .
Sep-05 1111 2 .
Oct-05 1111 2 .
Nov-05 1111 3 Nov-05
Dec-05 1111 3 .
Jan-06 1111 3 .
Need
Reporting_Date Account_ID Advance Latest_Advance
Jan-05 1111 0 .
Feb-05 1111 0 .
Mar-05 1111 1 Mar-05
Apr-05 1111 1 Mar-05
May-05 1111 1 Mar-05
Jun-05 1111 1 Mar-08
Jul-05 1111 2 Jul-05
Aug-05 1111 2 Jul-05
Sep-05 1111 2 Jul-05
Oct-05 1111 2 Jul-05
Nov-05 1111 3 Nov-05
Dec-05 1111 3 Nov-05
Jan-06 1111 3 Nov-05
Would be greatful of any help or ideas.
Assuming that dates should not carry forward across ID values:
data want;
set have;
by Account_ID;
retain new_date;
if first.Account_ID or Latest_Advance > . then new_date = Latest_Advance;
drop Latest_Advance;
rename new_date = Latest_Advance;
run;
This approach (and probably all approaches) requires that your data set is properly sorted (by ACCOUNT_ID Reporting_Date).
Post test data in the form of a datastep!
You can do this any number of ways, simplest maybe:
data want; set have (drop=latest_advance) have (where=(latest_advance ne .)); by reporting_date account_id advance; run;
data have;
input Reporting_Date $ Account_ID Advance Latest_Advance $;
datalines;
Jan-05 1111 0 .
Feb-05 1111 0 .
Mar-05 1111 1 Mar-05
pr-05 1111 1 .
May-05 1111 1 .
Jun-05 1111 1 .
Jul-05 1111 2 Jul-05
Aug-05 1111 2 .
Sep-05 1111 2 .
Oct-05 1111 2 .
Nov-05 1111 3 Nov-05
Dec-05 1111 3 .
Jan-06 1111 3 .
;
data want;
length temp $10;
call missing(temp);
do until(last.account_id);
set have;
by account_id;
if not missing(Latest_Advance) then temp=Latest_Advance;
else if missing(Latest_Advance) then Latest_Advance=temp;
output;
end;
drop temp;
run;
Regards,
Naveen Srinivasan
Assuming that dates should not carry forward across ID values:
data want;
set have;
by Account_ID;
retain new_date;
if first.Account_ID or Latest_Advance > . then new_date = Latest_Advance;
drop Latest_Advance;
rename new_date = Latest_Advance;
run;
This approach (and probably all approaches) requires that your data set is properly sorted (by ACCOUNT_ID Reporting_Date).
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.