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.
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;
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 */
This should work:
data want;
set have;
by id;
inv = td - lag(td);
if first.id then inv = td - cd;
run;
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;
Yes, that looks right.
thank you.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.