The input data looks like below:
State | County | HH | Var |
1 | 1 | 1 | 1 |
1 | 1 | 2 | 2 |
1 | 1 | 3 | |
1 | 2 | 1 | 4 |
1 | 2 | 2 | 5 |
1 | 3 | 1 | 6 |
2 | 1 | 1 | 1 |
2 | 1 | 2 | 2 |
2 | 2 | 1 | 3 |
2 | 3 | 1 | 4 |
2 | 3 | 2 |
The set of 3 variables state, county, and households (HH) specifies unique codes for observations.
The variable Var is, say, a characteristic of households and it has missing data. My question is: How can I replace those missing observations with the mean values of households of the same county?
The output data should look like:
State | County | HH | Var |
1 | 1 | 1 | 1 |
1 | 1 | 2 | 2 |
1 | 1 | 3 | 1.5 |
1 | 2 | 1 | 4 |
1 | 2 | 2 | 5 |
1 | 3 | 1 | 6 |
2 | 1 | 1 | 1 |
2 | 1 | 2 | 2 |
2 | 2 | 1 | 3 |
2 | 3 | 1 | 4 |
2 | 3 | 2 | 4 |
After some search, it seems quite straightforward with PROC STANDARD. Sorry for bringing this up, but for those who are as novice:
proc standard data=have replace;
var Var;
by state county;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.