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);
... View more
