assigning previous eff_date to current row for each acct_no

[Solly7]

Hi, i need to create new column previous_date for each acct_no, if current row is the first observation for particular acct_no then previous_date should be null, otherwise it should be the 

observation(eff_date) from previous row, on the screenshot of results on previous_date, the value should be 21Jul2023 on row3 instead of null and row4 should be 30Jun2023         

 

data have;
input ACCT_NO EFF_DATE : date9. ;
format EFF_DATE date9.;
datalines;
1 21JUL2023
1 25JUL2023
2 30JUN2023
2 10JUL2023
2 01AUG2023
;
run;


proc sort data=have;
by ACCT_NO EFF_DATE;
run;


data want;
set have;
by ACCT_NO;

if first.ACCT_NO then previous_date = .;

else previous_date = lag(EFF_DATE);

format previous_date date9.;

run;

[PaigeMiller · Accepted solution]

data have;
input ACCT_NO EFF_DATE : date9. ;
format EFF_DATE date9.;
datalines;
1 21JUL2023
1 25JUL2023
2 30JUN2023
2 10JUL2023
2 01AUG2023
;
data want;
    set have;
    by acct_no;
    lag_date=lag(eff_date);
    if first.acct_no then previous_date=.;
    else previous_date=lag_date;
    drop lag_date;
    format previous_date date9.;
run;

[mkeintz]

The lag function is NOT a "lookback".  It is a queue management function.  What you need is to always update the queue, but not always keep the results.

The IFN function lets you do this, as in:

data have;
input ACCT_NO EFF_DATE : date9. ;
format EFF_DATE date9.;
datalines;
1 21JUL2023
1 25JUL2023
2 30JUN2023
2 10JUL2023
2 01AUG2023
;
data want;
    set have;
    by acct_no;
    previous_date=ifn(first.acct_no,.,lag(eff_date));
    format previous_date date9.;
run;

The advantage of the IFN function is that both results (the 2nd and 3rd arguments) are executed, and then one of them is chosed based in the first argument.  So the lag is always updated, but not always returned to previous_date.

[Solly7]

thank you for clarity and noted