topic Re: Retaining Date value until change in SAS Programming

Retaining Date value until change

Adnan_Razaq — Tue, 16 May 2017 09:04:10 GMT

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.

Re: Retaining Date value until change

RW9 — Tue, 16 May 2017 09:14:05 GMT

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;

Re: Retaining Date value until change

novinosrin — Tue, 16 May 2017 09:16:12 GMT

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

Re: Retaining Date value until change

Astounding — Tue, 16 May 2017 10:32:00 GMT

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).