Lagging

[RandyStan]

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

[PeterClemmensen]

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 

[ErikLund_Jensen]

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;

[novinosrin]

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.

[ed_sas_member]

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;

[unison]

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;