Hello! I am a beginner SAS programmer and I am trying to update one of my datasets. I am trying to write code that will instruct SAS to search through a specific range of variables (Var1-Var200) in a dataset and look for a specific value (-87). If SAS finds that value present anywhere in that column, I want all the observations in that specific column to be replaced with that specified value. If possible, I would also like SAS to output a list of all the variables that were affected/updated.
My current dataset looks like this:
Var1 | Var2 | Var3 | Var4 | Var5 |
1 | 0 | 1 | 1 | 0 |
0 | 1 | 1 | 1 | 0 |
1 | 0 | 0 | 0 | 1 |
1 | 0 | 1 | -87 | 1 |
0 | 0 | 0 | 1 | 0 |
I want to update the dataset to look like this:
Var1 | Var2 | Var3 | Var4 | Var5 |
1 | 0 | 1 | -87 | 0 |
0 | 1 | 1 | -87 | 0 |
1 | 0 | 0 | -87 | 1 |
1 | 0 | 1 | -87 | 1 |
0 | 0 | 0 | -87 | 0 |
If possible, I am hoping to also create an additional output saying something like:
Variables that were updated |
Var4 |
I have tried using ARRAY and the FIND function to loop through the list of variables and update, but I don't know how to code it to update previous entries/rows of the dataset. I am also unsure how to write code that will output a list of the variables that were changed.
data want;
set have;
array v Var1-Var200;
do i=1 to dim(v);
if find(v(i), '-87') ge 1 then v(i)='-87';
end;
drop i;
run;
I appreciate any and all feedback! Thank you!
/* UNTESTED CODE */
proc summary data=have;
var var1-var200;
output out=_minimums_ min=min1-min200;
run;
data want;
if _n_=1 then set _minimums_;
set have;
array v var1-var200;
array m min1-min200;
do i=1 to dim(v);
if m(i)=-87 then v(i)=-87;
end;
drop min1-min200 _type_ _freq_;
run;
Would it make even more sense (it might in some situations) to replace the -87 with a SAS missing value?
So, before I go ahead and suggest a method, could there be -88 or -89 or -90 or ...
in a column that is NOT gong to trigger this? Is this because (I have actually seen similar things) the -87 indicates some sort of special value? Can we just search for columns whose MINIMUM value is -87?
/* UNTESTED CODE */
proc summary data=have;
var var1-var200;
output out=_minimums_ min=min1-min200;
run;
data want;
if _n_=1 then set _minimums_;
set have;
array v var1-var200;
array m min1-min200;
do i=1 to dim(v);
if m(i)=-87 then v(i)=-87;
end;
drop min1-min200 _type_ _freq_;
run;
Would it make even more sense (it might in some situations) to replace the -87 with a SAS missing value?
Data set named _MINIMUMS_ has that information. You can print it any way you want.
I see that now. Thanks so much!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.