topic Re: Lagging in SAS Programming

Lagging

RandyStan — Sun, 02 Feb 2020 14:12:47 GMT

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

Re: Lagging

PeterClemmensen — Sun, 02 Feb 2020 14:31:40 GMT

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

Re: Lagging

ErikLund_Jensen — Sun, 02 Feb 2020 14:35:51 GMT

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;

Re: Lagging

novinosrin — Sun, 02 Feb 2020 15:13:03 GMT

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.

Re: Lagging

ed_sas_member — Sun, 02 Feb 2020 15:16:46 GMT

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;

Re: Lagging

unison — Sun, 02 Feb 2020 18:05:34 GMT

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;