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;
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.