Hi SAS Experts,
I want to calculate mean of a dependent variable by categories of character variables and then replace categories with mean score. In other words, the new variables will be created with suffix _C for categorical variables and it contains mean score of dependent variable for each categories. I have written a sas macro but i am having difficulty executing last step i.e. replacing mean scores by categories. I know this can be done with 'call execute'.
data abcd;
input x y$ z a$;
cards;
73.9 C 12 E
15.96 B 20 F
92.84 A 9 E
88.4 D 16 F
40.44 C 4 B
9.85 B 9 C
54.79 B 2 C
88.19 E 16 E
98.04 D 18 F
35.85 F 15 F
15.69 E 3 F
88.6 F 16 C
33.71 B 14 A
83.19 C 3 D
68.99 E 5 C
28.15 B 17 A
56.92 A 14 A
84.21 A 13 D
91.07 E 7 A
51.76 E 4 F
57.38 D 16 A
69.96 E 3 C
17.81 D 20 A
18.59 E 6 A
17.31 C 4 A
;
run;
I want all categories of variable 'a' to be recoded with the following values (mean scores) into a new column(suffix _c). The same should be done for variable 'y' with their mean scores.
| a |
a_c |
| A |
40.1175 |
| B |
40.44 |
| C |
58.438 |
| D |
83.7 |
| E |
84.9767 |
| F |
50.95 |
%macro categ(input=, depvar=,output=);
*Get library and dataset name;
data _null_;
call symput ("library", put(upcase(substr("&input",1,index("&input",'.')-1)), $8.));
call symput ("datset", put(upcase(substr("&input",index("&input",'.')+1,length("&input"))), $32.));
run;
proc sql noprint;
select name into : vars separated by " "
from dictionary.columns
where LIBNAME = "WORK"
and MEMNAME = "ABCD"
and type = "char";
quit;
%let n=%sysfunc(countw(&vars));
%do i=1 %to &n;
%let val = %scan(&vars,&i);
proc means data = abcd noprint nway;
class &val.;
var &depvar.;
output out= temp_&val. (drop = _type_ _freq_) mean= ;
run;
%end;
%mend;
%categ(input=work.abcd, depvar=x, output=abcd2);
