topic Re: assigning previous eff_date to current row for each acct_no in SAS Programming

assigning previous eff_date to current row for each acct_no

Solly7 — Mon, 19 Feb 2024 18:34:00 GMT

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;

Re: assigning previous eff_date to current row for each acct_no

PaigeMiller — Mon, 19 Feb 2024 18:40:24 GMT

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;

Re: assigning previous eff_date to current row for each acct_no

mkeintz — Mon, 19 Feb 2024 21:55:55 GMT

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.

Re: assigning previous eff_date to current row for each acct_no

Solly7 — Tue, 20 Feb 2024 08:08:15 GMT

thank you for clarity and noted