many conditional lag

soumri — Thu, 02 Jun 2016 10:32:02 GMT

Hello everybody,

can anyone help me for this:

I have a form file:

ID CD NA TD val

BV01 12/05/2005 1 03/06/2007 13

BV01 12/05/2005 1 08/07/2007 15

BV01 12/05/2005 1 21/08/2007 14

BV01 12/05/2005 1 17/10/2007 12

BV01 12/05/2005 1 12/11/2007 09

BV01 12/05/2005 1 09/12/2007 09

BV01 12/05/2005 1 13/01/2008 00

BV01 12/05/2005 2 18/03/2008 17

BV01 12/05/2005 2 23/04/2008 17

BV01 12/05/2005 2 12/06/2008 15

BV01 12/05/2005 2 22/07/2008 13

BV01 12/05/2005 2 01/08/2008 10

BV01 12/05/2005 2 06/09/2008 07

BV03 02/11/2006 2 03/12/2008 12

BV03 02/11/2006 2 17/01/2009 13

BV03 02/11/2006 2 03/03/2009 13

BV03 02/11/2006 2 15/04/2009 10

BV03 02/11/2006 2 02/06/2009 07

BV03 02/11/2006 2 07/07/2009 07

BV03 02/11/2006 2 26/08/2009 00

BV04 02/01/2007 1 15/04/2009 19

BV04 02/01/2007 1 02/06/2009 19

BV04 02/01/2007 1 07/07/2009 17

BV04 02/01/2007 1 26/08/2009 13

BV04 02/01/2007 1 15/10/2009 10

BV04 02/01/2007 1 15/11/2009 09

BV04 02/01/2007 1 20/12/2009 08

BV04 02/01/2007 1 31/01/2010 07

BV04 02/01/2007 1 02/03/2010 00

what i want:

ID CD NA TD val inv

BV01 12/05/2005 1 03/06/2007 13 /*=TD-CD because it's the first TD for the ID=BV01 in an NA=1

BV01 12/05/2005 1 08/07/2007 15 /*=TD-lag(TD) beacuase it's the second TD for the ID=BV01 and NA=1

BV01 12/05/2005 1 21/08/2007 14 "

BV01 12/05/2005 1 17/10/2007 12 "

BV01 12/05/2005 1 12/11/2007 09 "

BV01 12/05/2005 1 09/12/2007 09 "

BV01 12/05/2005 1 13/01/2008 00 "

BV01 12/05/2005 2 18/03/2008 17 /*=TD-CD because it's the first TD for the ID=BV01 in an NA=2

BV01 12/05/2005 2 23/04/2008 17 /*=TD-lag(TD) beacuase it's the second TD for the ID=BV01 & NA=2

BV01 12/05/2005 2 12/06/2008 15 "

BV01 12/05/2005 2 22/07/2008 13 "

BV01 12/05/2005 2 01/08/2008 10 "

BV01 12/05/2005 2 06/09/2008 07 "

BV03 02/11/2006 2 03/12/2008 12 /*=TD-CD because it's the first TD for the ID=BV03 in an NA=2

BV03 02/11/2006 2 17/01/2009 13 /*=TD-lag(TD) beacuase it's the second TD for the ID=BV03 & NA=2

BV03 02/11/2006 2 03/03/2009 13 "

BV03 02/11/2006 2 15/04/2009 10 "

BV03 02/11/2006 2 02/06/2009 07 "

BV03 02/11/2006 2 07/07/2009 07 "

BV03 02/11/2006 2 26/08/2009 00 "

BV04 02/01/2007 1 15/04/2009 19 /*=TD-CD because it's the first TD for the ID=BV04 in an NA=1

BV04 02/01/2007 1 02/06/2009 19 /*=TD-lag(TD) beacuase it's the second TD for the ID=BV04 & NA=1

BV04 02/01/2007 1 07/07/2009 17 "

BV04 02/01/2007 1 26/08/2009 13 "

BV04 02/01/2007 1 15/10/2009 10 "

BV04 02/01/2007 1 15/11/2009 09 "

BV04 02/01/2007 1 20/12/2009 08 "

BV04 02/01/2007 1 31/01/2010 07 "

BV04 02/01/2007 1 02/03/2010 00 "

....

evry time that ID change or NA change the value of INV takes TD-CD, in next observations (for same ID in the same NA) INV takes TD-lag(TD).

help me please With many thanks.

Re: many conditional lag

soumri — Thu, 02 Jun 2016 10:43:08 GMT

I tried this, But did not work:

/*the data is sorted by ID and NA */

if first.na then na=0;nc+1;

lagtd=lag1(TD);
if nc=1 then inv=dc-dv;
if nc>1 then inv=dc-lagtd;

/* what is wrong. I am beginning with SAS */

Re: many conditional lag

Astounding — Thu, 02 Jun 2016 10:44:16 GMT

This should work:

data want;

set have;

by id;

inv = td - lag(td);

if first.id then inv = td - cd;

run;

Re: many conditional lag

soumri — Thu, 02 Jun 2016 11:02:27 GMT

no sorry it doesn't work, For the same individual (ID) with a two activity (NA) 1 & 2: for NA=1 inv value for the first observation is OK: (td-cd) for NA=2, the first value of inv takes td-lag(td)

Re: many conditional lag

soumri — Thu, 02 Jun 2016 11:58:03 GMT

I think this one work correctly:

data want;

set have;

by id na;

inv = td - lag(td);

if first.na then inv = td - cd;

run;

Re: many conditional lag

Astounding — Thu, 02 Jun 2016 12:07:03 GMT

Yes, that looks right.

Re: many conditional lag

soumri — Thu, 02 Jun 2016 13:05:01 GMT

thank you.

topic Re: many conditional lag in SAS Programming

many conditional lag

Re: many conditional lag

Re: many conditional lag

Re: many conditional lag

Re: many conditional lag

Re: many conditional lag

Re: many conditional lag