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!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.