Hello, can someone please advise me on the best way to perform the following (whether through macro, array, and/or do loop)?
For each variable, I want to search through all observations in the column and see if there is a "<" symbol. For variables with such a symbol, I want to completely ignore the number following the "<" and instead replace this entire observation with the minimum observation (smallest value) in the entire column. For variables without this symbol, I don't need to do anything and want to leave the data as is.
ID VAR1 VAR2
1 853.1 <12.1 (replace this with the smallest value in the entire VAR2 column, ignoring 12.1 and any other observations that have "<")
2 173.4 888.3
3 481.6 385.2
I attempted a macro, but it is not working, and now I wondering if an array or do loop would work better?
The issue is that the variables WITH "<" are registered as characters by SAS, while the variables WITHOUT "<" are seen as numeric. And I have many variables, so I'm trying to figure out a way to efficiently apply code to all variables, leaving the variables without "<" alone.
INCORRECT MACRO
%macro imputation (var);
data tab1_&var; set data (keep = ID &var);
if not (find(&var,'<')) then do;
&var_good = 111111; end; /* essentially don't need to do anything here, but I didn't know how to get SAS to "do nothing" */
else if find(&var,'<') then do;
&var = 999999; end;
&var_ = &var * 1; /* convert to numeric */
drop &var &var_good; run;
proc sql;
create table tab2_&var as
select *, min(&var_) as min_&var /* find the min observation in entire column, create a new column with this value, and attach to the previous dataset*/
from tab1_&var;
quit;
data tab3_&var; set tab2_&var;
if &var_ = 999999 then &var = min_&var; /* only assign the minimum observation to cells that had a "<" */
else &var = &var_; run;
data table_&var; set tab3_&var (keep = ID &var); run;
proc sort data=table_&var;
by ID ;
run;
%mend;
run;
%imputation(var1);
%imputation(var2);
%imputation(var3);
(The final step will be horizontally merge all the sorted datasets by ID... so that all data is now correct and complete).
Any guidance would be greatly appreciated! Thank you!!!
... View more