Hi, all,
I am always marveled at your expertise and I do appreciate your help.
My original dataset runs as follows:
PERSON | CUSIP6 | YEAR | Diff |
3 | A | 1987 | 1 |
3 | A | 1988 | 1 |
3 | A | 1989 | 1 |
3 | A | 1996 | 7 |
3 | A | 1997 | 1 |
3 | A | 1998 | 1 |
12 | B | 1994 | 1 |
12 | B | 1995 | 1 |
12 | B | 1996 | 1 |
12 | B | 1997 | 1 |
13 | C | 2007 | 1 |
13 | C | 2008 | 1 |
13 | C | 2011 | 3 |
13 | C | 2012 | 1 |
13 | D | 1996 | 1 |
13 | D | 1997 | 1 |
13 | D | 1998 | 1 |
The by-group here is PERSON-CUSIP6.
The very first observation by PERSON-CUSIP6 is always 1.
The following observations can be 1's, or an integer larger than 1. But right after it, observations regain 1's.
What I want to create is, if a value larger than 1 appears, I want to replace the subsequent values (1's) with that value (which is larger than 1) by each by group PERSON-CUSIP6.
So, the results will look like this:
PERSON | CUSIP6 | YEAR | Diff | Diff_k |
3 | A | 1987 | 1 | 1 |
3 | A | 1988 | 1 | 1 |
3 | A | 1989 | 1 | 1 |
3 | A | 1996 | 7 | 7 |
3 | A | 1997 | 1 | 7 |
3 | A | 1998 | 1 | 7 |
12 | B | 1994 | 1 | 1 |
12 | B | 1995 | 1 | 1 |
12 | B | 1996 | 1 | 1 |
12 | B | 1997 | 1 | 1 |
13 | C | 2007 | 1 | 1 |
13 | C | 2008 | 1 | 1 |
13 | C | 2011 | 3 | 3 |
13 | C | 2012 | 1 | 3 |
13 | D | 1996 | 1 | 1 |
13 | D | 1997 | 1 | 1 |
13 | D | 1998 | 1 | 1 |
Thank you in advance! Wish you a wonderful weekend!
KS ~
You ask a lot of questions today. Would you like to pay us ?
data have;
infile cards expandtabs truncover;
input PERSON CUSIP6 $ YEAR Diff;
cards;
3 A 1987 1
3 A 1988 1
3 A 1989 1
3 A 1996 7
3 A 1997 1
3 A 1998 1
12 B 1994 1
12 B 1995 1
12 B 1996 1
12 B 1997 1
13 C 2007 1
13 C 2008 1
13 C 2011 3
13 C 2012 1
13 D 1996 1
13 D 1997 1
13 D 1998 1
;
data want;
set have;
retain diff_k;
by person cusip6;
if first.cusip6 then diff_k=.;
diff_k=max(diff_k,diff);
run;
You ask a lot of questions today. Would you like to pay us ?
data have;
infile cards expandtabs truncover;
input PERSON CUSIP6 $ YEAR Diff;
cards;
3 A 1987 1
3 A 1988 1
3 A 1989 1
3 A 1996 7
3 A 1997 1
3 A 1998 1
12 B 1994 1
12 B 1995 1
12 B 1996 1
12 B 1997 1
13 C 2007 1
13 C 2008 1
13 C 2011 3
13 C 2012 1
13 D 1996 1
13 D 1997 1
13 D 1998 1
;
data want;
set have;
retain diff_k;
by person cusip6;
if first.cusip6 then diff_k=.;
diff_k=max(diff_k,diff);
run;
Hello, @KS99
You already asked this question and received an answer that you marked correct. https://communities.sas.com/t5/SAS-Programming/How-to-replace-the-subsequent-rows-with-a-value-right...
Please don't ask the same question more than once.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.