SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to assign mean values of groups to missing observations?

Reply
Frequent Contributor
Posts: 75

How to assign mean values of groups to missing observations?

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

 

 

Frequent Contributor
Posts: 75

Re: How to assign mean values of groups to missing observations?

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;

Super User
Posts: 5,256

Re: How to assign mean values of groups to missing observations?

Thanks for sharing your solution!
Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 300 views
  • 2 likes
  • 2 in conversation