Dear All
My data is as follows
Date ID VarA
01JAN2019 A 45
02JAN2019 A 16
05JAN2019 A 27
09JAN2019 A 45
08JAN2019 B 65
10JAN2019 B 14
15MAR2019 B 26
09SEP2019 B 25
25OCT2019 B 25
I want to construct a lag_VARA and resset the counter to 0 after the date 01SEP2019. 01SEP2019 is an example of the date from which the data becomes available once again.
So the data I want would look as follows:
Date ID VarA lag_VARA
01JAN2019 A 45 .
02JAN2019 A 16 45
05JAN2019 A 27 16
09JAN2019 A 45 27
08JAN2019 B 65 .
10JAN2019 B 14 65
15MAR2019 B 26 14
09SEP2019 B 25 .
25OCT2019 B 25 25
Please help.
Randy
Here is one way
data have;
input Date :date9. ID $ VarA;
format Date date9.;
datalines;
01JAN2019 A 45
02JAN2019 A 16
05JAN2019 A 27
09JAN2019 A 45
08JAN2019 B 65
10JAN2019 B 14
15MAR2019 B 26
09SEP2019 B 25
25OCT2019 B 25
;
data want;
set have;
by ID;
lag_VARA = ifn (lag(Date) <= '01Sep2019'd & Date > '01Sep2019'd | first.id, ., lag(VarA));
run;
Result:
Date ID VarA lag_VARA 01JAN2019 A 45 . 02JAN2019 A 16 45 05JAN2019 A 27 16 09JAN2019 A 45 27 08JAN2019 B 65 . 10JAN2019 B 14 65 15MAR2019 B 26 14 09SEP2019 B 25 . 25OCT2019 B 25 25
Hi @RandyStan
Use Lag(date) to find first obs in or after 01sep2019.
data have;
input Date date9. ID $ VarA;
cards;
01JAN2019 A 45
02JAN2019 A 16
05JAN2019 A 27
09JAN2019 A 45
08JAN2019 B 65
10JAN2019 B 14
15MAR2019 B 26
09SEP2019 B 25
25OCT2019 B 25
run;
data want (drop=lagDate); set have; by ID;
lagDate = lag(Date);
lagVarA = lag(VarA);
if first.ID or (Date > '01sep2019'd and lagDate <= '01sep2019'd) then lagVarA = .;
run;
data have;
input Date :date9. ID $ VarA;
format Date date9.;
datalines;
01JAN2019 A 45
02JAN2019 A 16
05JAN2019 A 27
09JAN2019 A 45
08JAN2019 B 65
10JAN2019 B 14
15MAR2019 B 26
09SEP2019 B 25
25OCT2019 B 25
;
data want;
set have;
by id;
lag_VARA =lag(varA);
retain _iorc_ '01sep2019'd;
if lag(date)<=_iorc_ and date>_iorc_ or first.id then lag_VARA=.;
run;
Notes:
1. Get the lags as usual with straight forward LAG function within a BY group
2. 01SEP2019 is a constant filter and so conveniently store and retain in a temp variable for conditional check
3. Reset the LAG to missing soon as the current date is greater then the constant date value.
4. _IORC_ conveniently retains the constant date and is not written to the output dataset.
Hi @RandyStan
You can do this. The only thing is to create the lagged value in an assignment statement before using it in a conditional statement (IF).
Best,
data want;
set have;
by ID;
_lagDate = lag(Date);
lagVarA = lag(VarA);
if first.ID or (Date > '01sep2019'd and _lagDate <= '01sep2019'd) then lagVarA = .;
drop _lagDate;
run;
Similar to others:
data want;
set have;
by id;
_ldate=lag(date);
_lvarA=lag(varA);
if not(first.id) and not(_ldate le '01SEP2019'd le date) then
lag_varA=_lvarA;
drop _:;
run;
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.