DATA Step, Macro, Functions and more

Retaining Date value until change

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Retaining Date value until change

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.


Accepted Solutions
Solution
‎05-16-2017 06:55 AM
Super User
Posts: 5,518

Re: Retaining Date value until change

Posted in reply to Adnan_Razaq

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: Retaining Date value until change

Posted in reply to Adnan_Razaq

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;
PROC Star
Posts: 283

Re: Retaining Date value until change

Posted in reply to Adnan_Razaq

 

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

Solution
‎05-16-2017 06:55 AM
Super User
Posts: 5,518

Re: Retaining Date value until change

Posted in reply to Adnan_Razaq

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 141 views
  • 0 likes
  • 4 in conversation