I am trying to create a macro that drops variables having missing values % greater than 90. It is working fine for numeric variables. Since PROC MEANS does not work for character variables and PROC FREQ produces clumsy output, i ain't able to replice it for character variables. data have;    set sashelp.class;    if _n_ in (5 10 12) then do;       call missing(height,weight);       end;    if _n_ in (7 9) then do;       call missing(age);       end;    r = ranuni(1);    run; ods output summary=nmiss; proc means data=have stackods nmiss n;    var _numeric_;    run; ods output close; proc print data=nmiss;    run; %let pmisslist=; proc sql noprint;    select variable into :pmisslist separated by ' '       from nmiss where nmiss/(sum(n,nmiss)) ge .15;    quit;    run; %put NOTE: &=PMISSLIST; ... View more

I am trying to create a macro that drops all the variables having missing value % greater than 80. I have written a small code to get it started. But i am stuck how to take it forward.   data have;   set sashelp.class;   if _n_ in (5,10,12) then do;   call missing(height,weight);   end;   if _n_ in (7,9) then do;   call missing(age);   end; run; proc means data=have noprint; var _numeric_; output out=temp (drop=_type_) nmiss=; run; ... View more

I have a macro that returns important variables. The demo output file from the macro looks like below : Cluster Variables R Square 1 ABCD 0.5 2 DEFG 0.7 3 ASSD 0.04 4 ADFG 0.0555 On average, the macro returns 200-300 variables from 1.2k variables. I want spearman correlation to be run on only on these returning 200 variables. I have a macro for spearman correlation. I want to merge both these two macros. ods select none; ods output spearmancorr=sp hoeffdingcorr=hoef; proc corr data=&dataset spearman hoeffding rank; var &vars; with attrition; run; ... View more

Hi Team, I am using a macro that cap outliers based on box-plot / turkey method. But the problem arises PROC MEANS truncates variable names and the same problem happens with PROC LOGISTIC. Example variable name : prod_INVT_OLD_BAL_AVG_ACT_1_3M This causes my macro to not working properly. %macro box(input=, vars=, output= ); %let QR=; %let Q1=; %let Q3=; %let varL=; %let varH=; %let n=%sysfunc(countw(&vars)); %do i= 1 %to &n; %let val = %scan(&vars,&i); %let QR = &QR &val._QRange; %let Q1 = &Q1 &val._P25; %let Q3 = &Q3 &val._P75; %let varL = &varL &val.L; %let varH = &varH &val.H; %end; /* Calculate the quartiles and inter-quartile range using proc univariate */ proc means data=&output nway noprint; var &vars; output out=temp QRANGE = P25= P75= / autoname; run; /* Extract the upper and lower limits into macro variables */ data temp; set temp; ID = 1; array vara(&n)  &QR; array varb(&n)  &Q1; array varc(&n)  &Q3; array lower(&n) &varL; array upper(&n) &varH; do i = 1 to dim(vara); lower(i) = varb(i) - 3 * vara(i); upper(i) = varc(i) + 3 * vara(i); end; drop i _type_ _freq_; run; data temp1; set &input; ID = 1; run; data &output; merge temp1 temp; by ID; array vars(&n) &vars; array lower(&n) &varL; array upper(&n) &varH; do i = 1 to dim(vars); if not missing(vars(i)) then do; if vars(i) < lower(i) then vars(i) = lower(i); if vars(i) > upper(i) then vars(i) = upper(i); end; end; drop &QR &Q1 &Q3 &varL &varH ID i; run; %mend; %box(input=abcd, vars = a b c d, output= test); ... View more

How can we use LEFT JOIN when we have more than 2 tables? PROC SQL equivalent code for the following merge statement code?l Data abc; merge a (in=X) b c; by id; if X; run; ... View more