Prev: Proc Format
Next: Check if a variable has any letter from A to Z. Is there a function for it? Thanks
From: SD on 10 Mar 2010 01:49
Hello All,

Data Have

year_month Name marks mean_marks
200901 jack 75
200902 jack 74
200903 jack 73 72.4
200904 jack 72
200905 jack 71
200901 jill 90
200902 jill 89 88
200903 jill 87
200904 jill 86
200905 jill 85
200906 jill 84 82
200907 jill 83
200908 jill 82


Data want
--------------
year_month Name marks mean_marks
200901 jack 75 72.4
200902 jack 74 72.4
200903 jack 73 72.4
200904 jack 72 72.4
200905 jack 71 72.4
200901 jill 90 88
200902 jill 89 88
200903 jill 87 88
200904 jill 86 88
200905 jill 85 88
200906 jill 84 82
200907 jill 83 82
200908 jill 82 82


Appreciate any ones help.

thanks
SD
From: Eli Y. Kling on 10 Mar 2010 03:21

* Option1 - if each Name has only one mean;
data FullData(keep=year_month Name marks index=(Name))
mean_marks(keep=year_month Name mean_marks index=(Name));
input year_month $ Name $ marks mean_marks;
if not(missing(mean_marks)) then output mean_marks;
output FullData;
datalines;
200901 jack 75 .
200902 jack 74 .
200903 jack 73 72.4
200904 jack 72 .
200905 jack 71 .
200901 jill 90 .
200902 jill 89 88
200903 jill 87 .
200904 jill 86 .
200905 jill1 85 .
200906 jill1 84 82
200907 jill1 83 .
200908 jill1 82 .
run;

data Wanted;
merge FullData mean_marks;
by Name;
run;



* Option2 - if each Name may have more than one mean;

data FullData;
input year_month $ Name $ marks mean_marks;
datalines;
200901 jack 75 .
200902 jack 74 .
200903 jack 73 72.4
200904 jack 72 .
200905 jack 71 .
200901 jill 90 .
200902 jill 89 88
200903 jill 87 .
200904 jill 86 .
200905 jill 85 .
200906 jill 84 82
200907 jill 83 .
200908 jill 82 .
run;

proc sort data=FullData;
by Name year_month;
run;

data FullData;
set FullData;
by Name year_month;
retain EliAux1; drop EliAux1;
if first.name then EliAux1=.;
if not missing(mean_marks) then EliAux1=mean_marks;
else mean_marks=EliAux1;
run;

proc sort data=FullData;
by Name descending year_month;
run;

data FullData;
set FullData;
by Name descending year_month;
retain EliAux1; drop EliAux1;
if first.name then EliAux1=.;
if not missing(mean_marks) then EliAux1=mean_marks;
else mean_marks=EliAux1;
run;

proc sort data=FullData;
by Name year_month;
run;

 | 
Pages: 1
Prev: Proc Format
Next: Check if a variable has any letter from A to Z. Is there a function for it? Thanks