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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.