topic I have 2 data sets one with mean value for each variable(proc means output) and the other with values in SAS Programming

I have 2 data sets one with mean value for each variable(proc means output) and the other with values

venkatard — Mon, 15 Apr 2013 11:47:09 GMT

I have 2 data sets one with mean value for each variable(proc means output) and the other with values. I want to replace the missing values with mean value for each particular variable in the dataset.

Any help please.

I tried call symput but does it work for multiple variables?

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

kuridisanjeev — Mon, 15 Apr 2013 12:10:36 GMT

Hi..

Not sure how you going to accomplish this with Call Symput..If possible can you provide your code which you have tried???

Other then that coalesce function will be helpful for your requirement.

Regards.

Sanjeev.K

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

allurai0412 — Mon, 15 Apr 2013 17:07:34 GMT

hi ,

try this...but............please atleast post your working / sample datasets.....

data samp;
infile datalines missover;
input id sal ;
if missing(sal) then sal=symget("avgsal");
datalines;
001 5000
002 7000
003
004 7832
005
;
run;

proc sql noprint ;
select avg(sal) into : avgsal from samp ;
run;

Regards

ALLU

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

data_null__ — Mon, 15 Apr 2013 17:20:49 GMT

PROC STDIZE, for 1 or more variables in the VAR statement and BY groups if you like.

data samp;
   infile datalines missover; 
   input id:$3. sal ;
   datalines; 
001 5000
002 7000
003 
004 7832
005 
;;;;
   run; 
proc stdize reponly missing=mean out=poked;
   var sal;
   run;

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

Ron_MacroMaven — Mon, 15 Apr 2013 17:29:34 GMT

1. read in the data structure of your data.

make an array of all the numeric variables

2. read in the single row of the Means data set

whose variable have been autonamed

so that Mean is the prefix of each variable.

The issues are getting the names of the variables aligned with their means.

so that the array references are correct.

alternately you can use sql to extract the list of variable

and feed that list to Summary

which you can then use in the array statements.

DATA Update;*todo: (drop = Mean:);

*get the data structure for the array Valu;

if 0 then set Library.Mydata;

array Valu(*) _numeric_;

*read the Means data set -- one row -- into the set of values for updating;

set Means;

array Mean(*);

do until(EndoFile);

set Library.MyData end = EndoFile;

do i = 1 to dim(Valu);

if Valu(I) eq . then Valu(I) = Mean(I);

end;

output;

end;

stop;

run;

PROC SQL; describe table &syslast;

quit;

Ron Fehd matching maven

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

Sudhakar_A — Tue, 16 Apr 2013 06:00:22 GMT

you can merge this 2 datasets with by variables which you have used in proc means to create multiple means values. then you can assign the mean values to missing values if needed.

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

venkatard — Tue, 16 Apr 2013 06:29:39 GMT

I think i will give clear idea.

First dataset with means.

mean_score mean_forum mean_subs

23.45 43.45 12.22

Second data set with original values.

Score forum subs

12 23 .

145 12 87

. 12 98

15 . 67

Now i want to replace the missing values in second data set with the mean value.

For example in score if any value is missing it should be replaced with mean_score.

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

Sudhakar_A — Tue, 16 Apr 2013 06:46:54 GMT

take all mean values to respective Macro variables. Then in the next step use symget or simple if condition to replace the values.

Data _null_;

set mean_data;

call symput("Mscore", strip(put(mean_score)));

call symput("Mforum", strip(put(mean_forum)));

call symput("Msubs", strip(put(mean_subs)));

run;

data hh;

set values;

if score=. then score=&mean_score;

else score=score;

....

run;

do the same for other variables, Hope this helps

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

Ron_MacroMaven — Tue, 16 Apr 2013 10:45:44 GMT

I have posted a general solution on SAS community wiki:

http://www.sascommunity.org/wiki/Replacing_Missing_with_Mean

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

Ron_MacroMaven — Tue, 16 Apr 2013 18:42:33 GMT

On Tue, 16 Apr 2013 17:45:04 +0100, Ian

<ian.wakeling@HANANI.QISTATS.CO.UK> wrote:

>How about using PROC STDIZE?

proc stdize data = old

out = new

missing = mean

reponly;

var _numeric_;

run;

>You can also impute with the median or read values from a second data set.

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

venkatard — Thu, 18 Apr 2013 06:12:21 GMT

This works Ron.

Thank:-)

Re: I have 2 data sets one with mean value for each variable(proc means output) and the other with values

Ksharp — Sat, 20 Apr 2013 09:40:14 GMT

It is easy :

data want;

if _n_ eq 1 then set mean_data;

set have ;

score=coalesce( score ,mean_score) ;

forum=coalesce( forum,mean_forum);

...........

run;

Ksharp