DATA Step, Macro, Functions and more

Mean imputation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Mean imputation

Hi,

Need help, I want to impue the data for grouped data based on variabke Region,

Data set:

CountryRegionAge
NigeriaAfrica15
EthiopiaAfrica 
EgyptAfrica42
Democratic Republic of CongoAfrica 
South AfricaAfrica13
RussiaEurope45
GermanyEurope54
FranceEurope17
United KingdomEurope60
ItalyEurope 

 

Africa Region mean : 23 and Europe Region mean :44

Expected Output:

CountryRegionAge
NigeriaAfrica15
EthiopiaAfrica23
EgyptAfrica42
Democratic Republic of CongoAfrica23
South AfricaAfrica13
RussiaEurope45
GermanyEurope54
FranceEurope17
United KingdomEurope60
ItalyEurope44

 I have so many Levels in (Regions), by writing if condition will take much time. So is there any other method ...?


Accepted Solutions
Solution
‎10-10-2017 02:54 AM
Super User
Posts: 10,618

Re: Missing value Imputation for Grouped Data

data have;
infile cards expandtabs truncover;
input Country	& $40. Region & $20.	Age;
cards;
Nigeria 	Africa	     15
Ethiopia	Africa	 
Egypt	Africa	  42
Democratic Republic of Congo	Africa	 
South Africa	Africa	 13
Russia	 Europe	 45
Germany 	Europe	     54
France	 Europe	 17
United Kingdom	 Europe	 60
Italy	Europe	
;
run;
proc stdize data=have out=want reponly missing=mean;
by region;
var age;
run;

View solution in original post


All Replies
Super Contributor
Posts: 320

Re: Mean imputation

Hello,

 

With SQL :

proc sql;
  CREATE TABLE want AS
  SELECT country, region,
         CASE WHEN age NOT IS MISSING THEN age ELSE round(mean(age)) END AS age
  FROM HAVE
  GROUP BY region;
quit;
Super User
Posts: 22,857

Re: Mean imputation

Look at PROC STDIZE 

Super Contributor
Posts: 500

Re: Mean imputation

And another double-post: https://communities.sas.com/t5/Base-SAS-Programming/Missing-value-Imputation-for-Grouped-Data/m-p/40...

 

Please don't post one question in multiple groups, this just causes confusion and those willing to help may waste time.

Occasional Contributor
Posts: 12

Missing value Imputation for Grouped Data

Posted in reply to andreas_lds

Hi,

Need help to impute the missing values by mean for the group data,

Data :

CountryRegionAge
NigeriaAfrica15
EthiopiaAfrica 
EgyptAfrica42
Democratic Republic of CongoAfrica 
South AfricaAfrica13
RussiaEurope45
GermanyEurope54
FranceEurope17
United KingdomEurope60
ItalyEurope

 

 

Mean of Africa=23 and Mean of Europe =44

Expected Output:

CountryRegionAge
NigeriaAfrica15
EthiopiaAfrica23
EgyptAfrica42
Democratic Republic of CongoAfrica23
South AfricaAfrica13
RussiaEurope45
GermanyEurope54
FranceEurope17
United KingdomEurope60
ItalyEurope44

 

 

Solution
‎10-10-2017 02:54 AM
Super User
Posts: 10,618

Re: Missing value Imputation for Grouped Data

data have;
infile cards expandtabs truncover;
input Country	& $40. Region & $20.	Age;
cards;
Nigeria 	Africa	     15
Ethiopia	Africa	 
Egypt	Africa	  42
Democratic Republic of Congo	Africa	 
South Africa	Africa	 13
Russia	 Europe	 45
Germany 	Europe	     54
France	 Europe	 17
United Kingdom	 Europe	 60
Italy	Europe	
;
run;
proc stdize data=have out=want reponly missing=mean;
by region;
var age;
run;
Super User
Posts: 22,857

Re: Mean imputation

Posted in reply to andreas_lds

@andreas_lds @Naveen1 please note that I've merged your duplicate posts into a single one. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 236 views
  • 6 likes
  • 5 in conversation