Date: Fri, 25 May 2007 09:57:51 -0500
Reply-To: Tom White <tw2@MAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Tom White <tw2@MAIL.COM>
Subject: SAS Data Step--Fill in missing values
Content-Type: text/plain; charset="iso-8859-1"
data foo;
input A $ 1-10
B $ 11-20
C $ 21-30
D best12.;
cards;
12 5B231 0
56 1003 0
19 7AS125 0
19 7AS125 0
19 7AS125 1260 1
19 7AS125 1260 1
32 909 0
65 3012 0
60 1601 0
60 1601 0
1601 6597 1
60 1601 6597 1
95 7AH014 0
95 7AH014 0
95 7AH014 838 1
95 7AH014 838 2
95 7AH014 838 3
95 7AH014 838 4
95 7AH014 838 1
95 7AH014 838 2
95 7AH014 838 3
95 7AH014 838 4
71 1518 0
71 1518 0
1518 6956 1
1518 6956 2
1518 6956 3
71 1518 6956 1
71 1518 6956 2
71 1518 6956 3
61 AS009 0
61 AS009 0
61 AS009 0
61 AS009 0
33 18541 0
33 18541 0
33 18541 0
66 B2741 0
86 2246 0
86 2246 0
2246 07436 1
2246 07436 2
2246 07436 3
2246 07436 4
86 2246 07436 1
86 2246 07436 2
86 2246 07436 3
13 264 0
87 2304 0
14 7275 0
25 110 0
25 110 0
110 1774 1
25 110 1774 1
run;
Hello SAS-L,
I have selected a few cases from a larger dataset to present my problem.
This data is simply member/customer account level data in health insurance industry.
I would like to fill in the missing values for variables A and C.
(Variables B and D is never missing.)
If you look at the data observe that:
A and C have some missing values.
EXAMPLES:
1. A=12 has a missing C. There is just no way to fill in this
case because we don't have enough information. Therefore we leave this one as is.
2. A=56 Same as 1.
3. A=19. Here, we can easily infer that C should be 1260.
(Looking also at D column might be of help.)
Therefore, the first two observations with A=56 should take on the value
1260 for C.
And so on.
4. Let's look at A=60. Here we have both A and C missing.
Again, you can easily see that the third missing obs of A=60 should be
60 (again notice how D is not 0, but a value other than 0, in this case, 1.
Just mentioning this if it is of any help.) and that the missing C should be
6597.
5.Similarly:
A=71 should be filled in with 71 and
C should be filled in with 6956
A=86 should be filled in with 86 and
C should be filled in with 07436
A=25 should be filled in with 25 and
C should be filled in with 1774
I have SAS v8.2
Thank you.
tom
=
Affordable Radiant Heat Do It Yourself
Radiant heating packages, diy install. Expert Design assistance free.
http://a8-asy.a8ww.net/a8-ads/adftrclick?redirectid=bdfab744df8e9c8bd3dbadb220614ffd
