Retaining Date value until change

[Adnan_Razaq]

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.

[RW9]

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;

[novinosrin]

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

[Astounding · Accepted solution]

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