Hi, greetings,
Thank you in advance for the help this time, and for all the previous helps as well.
The results I obtained by running the following code are the below:
DATA Treatment; set Treatment;
by cusip;
retain _days;
if not missing(days) then _days=days;
else days=_days; drop _days; run;
The original dataset I had was:
ANNDATS | CUSIP | days |
12/14/1988 | 00020910 | |
1/12/1989 | 00020910 | |
2/2/1989 | 00020910 | |
5/25/1989 | 00020910 | 2 |
6/8/1989 | 00020910 | |
7/25/1989 | 00020910 | |
8/31/1989 | 00020910 | -1 |
1/11/1990 | 00020910 | |
6/7/1990 | 00020910 | |
6/12/1990 | 00020910 | |
7/5/1990 | 00020910 | |
7/12/1990 | 00020910 | |
8/21/1990 | 00020910 | 0 |
9/12/1990 | 00020910 | |
9/18/1990 | 00020910 | |
9/26/1990 | 00020910 | |
5/31/1993 | 00020910 | 2 |
7/22/1993 | 00020910 | |
8/18/1993 | 00020910 | |
5/19/1982 | 00036110 | |
5/19/1983 | 00036110 | |
7/12/1983 | 00036110 |
Running the SAS code, I obtained:
ANNDATS | CUSIP | days |
12/14/1988 | 00020910 | |
1/12/1989 | 00020910 | |
2/2/1989 | 00020910 | |
5/25/1989 | 00020910 | 2 |
6/8/1989 | 00020910 | 2 |
7/25/1989 | 00020910 | 2 |
8/31/1989 | 00020910 | -1 |
1/11/1990 | 00020910 | -1 |
6/7/1990 | 00020910 | -1 |
6/12/1990 | 00020910 | -1 |
7/5/1990 | 00020910 | -1 |
7/12/1990 | 00020910 | -1 |
8/21/1990 | 00020910 | 0 |
9/12/1990 | 00020910 | 0 |
9/18/1990 | 00020910 | 0 |
9/26/1990 | 00020910 | 0 |
5/31/1993 | 00020910 | 2 |
7/22/1993 | 00020910 | 2 |
8/18/1993 | 00020910 | 2 |
5/19/1982 | 00036110 | 2 |
5/19/1983 | 00036110 | 2 |
7/12/1983 | 00036110 | 2 |
What I want to have is to produce the results in which the values of days from a previous CUSIP do not spill over to the values of the next CUSIP. In other words, I want to have the following correct results:
ANNDATS | CUSIP | days |
12/14/1988 | 00020910 | |
1/12/1989 | 00020910 | |
2/2/1989 | 00020910 | |
5/25/1989 | 00020910 | 2 |
6/8/1989 | 00020910 | 2 |
7/25/1989 | 00020910 | 2 |
8/31/1989 | 00020910 | -1 |
1/11/1990 | 00020910 | -1 |
6/7/1990 | 00020910 | -1 |
6/12/1990 | 00020910 | -1 |
7/5/1990 | 00020910 | -1 |
7/12/1990 | 00020910 | -1 |
8/21/1990 | 00020910 | 0 |
9/12/1990 | 00020910 | 0 |
9/18/1990 | 00020910 | 0 |
9/26/1990 | 00020910 | 0 |
5/31/1993 | 00020910 | 2 |
7/22/1993 | 00020910 | 2 |
8/18/1993 | 00020910 | 2 |
5/19/1982 | 00036110 | |
5/19/1983 | 00036110 | |
7/12/1983 | 00036110 |
Please give me a little clue, and
wish you a very good evening!
KS -,
First thing: When testing code or trying something you may not be 100 percent sure, with chocolate sprinkles, will work do not use code like
DATA Treatment; set Treatment;
When the same name is on the Set and Data statements the source data set is completely replaced. So there is no way to test if your code behaved correctly or not as you no longer have a before to compare with after. And if you try multiple things it may be so screwy as to not be recognizable at all. For one thing Retain does not do what you want if the variable you Retain is already in the data set.
Sort the data by CUSIP if it isn't already then use before the set/reset values of days and _days to remove a lingering _days from previous Cusip.
If first.cusip then call missing(_days);
First thing: When testing code or trying something you may not be 100 percent sure, with chocolate sprinkles, will work do not use code like
DATA Treatment; set Treatment;
When the same name is on the Set and Data statements the source data set is completely replaced. So there is no way to test if your code behaved correctly or not as you no longer have a before to compare with after. And if you try multiple things it may be so screwy as to not be recognizable at all. For one thing Retain does not do what you want if the variable you Retain is already in the data set.
Sort the data by CUSIP if it isn't already then use before the set/reset values of days and _days to remove a lingering _days from previous Cusip.
If first.cusip then call missing(_days);
In addition to resetting _days to missing when starting a new cusip, you might want to use this task to learn about the COALESCE function - which removes the need for the IF test:
data want (drop=_:);
set have;
by cusip;
retain _days;
if first.cusip then call missing(_days);
_days=coalesce(_days,days);
days=_days;
run;
Thank you mkeintz,
I've encountered coalese function many times before, but now in this context I come to understand it. If you don't mind, can you give me a little explanation on how
call missing(_days);
is supposed to work? (I never get call function)
Wish you a very good evening!
KS -.,
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.