BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KS99
Obsidian | Level 7

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 -, 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller
KS99
Obsidian | Level 7
Thank you, Sir,
It works perfectly!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1756 views
  • 0 likes
  • 2 in conversation