topic Filling in missing obs with conditions in SAS Programming

Filling in missing obs with conditions

KS99 — Mon, 16 May 2022 21:57:47 GMT

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

Re: Filling in missing obs with conditions

ballardw — Mon, 16 May 2022 22:22:15 GMT

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);

Re: Filling in missing obs with conditions

mkeintz — Tue, 17 May 2022 00:33:01 GMT

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;

Re: Filling in missing obs with conditions

KS99 — Tue, 17 May 2022 00:46:44 GMT

Thank you, ballardw,
Your code works perfectly.
I also copy your suggestions!

KS -,

Re: Filling in missing obs with conditions

KS99 — Tue, 17 May 2022 00:57:00 GMT

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