Hi all,
I am really marveled by your expertise and I also sincerely appreciate your help.
I have the following dataset:
ID CUSIP YEAR Diff
3 20030N 1987 1
3 20030N 1988 1
3 20030N 1989 1
3 20030N 1996 7
3 20030N 1997 1
3 20030N 1998 1
12 08658U 1994 1
12 08658U 1995 1
12 08658U 1996 1
12 08658U 1997 1
13 001547 2007 1
13 001547 2008 1
13 001547 2011 3
13 001547 2012 1
16 101137 1996 1
16 101137 1997 1
16 101137 1998 1
16 101137 1999 1
16 101137 2007 8
16 101137 2008 1
16 101137 2009 1
16 101137 2010 1
By group ID-Cusip, the "Diff" starts with 1. Subsequent values of Diff can either be 1's or a value larger than 1. But, even if a value larger than 1 appears, Diff regains 1 after that.
What I want to create is:
By ID-Cusip group, if a value larger than 1 appears, I want to replace all the subsequent 1's with that value (larger than 1).
So, the data I want would look like this:
ID CUSIP YEAR Diff Newvar
3 20030N 1987 1 1
3 20030N 1988 1 1
3 20030N 1989 1 1
3 20030N 1996 7 7
3 20030N 1997 1 7
3 20030N 1998 1 7
12 08658U 1994 1 1
12 08658U 1995 1 1
12 08658U 1996 1 1
12 08658U 1997 1 1
13 001547 2007 1 1
13 001547 2008 1 1
13 001547 2011 3 3
13 001547 2012 1 3
16 101137 1996 1 1
16 101137 1997 1 1
16 101137 1998 1 1
16 101137 1999 1 1
16 101137 2007 8 8
16 101137 2008 1 8
16 101137 2009 1 8
16 101137 2010 1 8
Thank you in advance!!
KS -,
data want;
set have;
by id cusip;
retain newvar;
if first.cusip then newvar=1;
if diff>1 then newvar=diff;
run;
This is a good place to use the RETAIN statement. The variable(s) in RETAIN statements keeps the value from the previous row, unless the variable is explicity assigned a new value.
data want;
set have;
by id cusip;
retain newvar;
if first.cusip then newvar=1;
if diff>1 then newvar=diff;
run;
This is a good place to use the RETAIN statement. The variable(s) in RETAIN statements keeps the value from the previous row, unless the variable is explicity assigned a new value.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.