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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.