topic Re: How to replace all previous values in a variable if a specific condition is met in SAS Programming

How to replace all previous values in a variable if a specific condition is met

BlakeDG — Mon, 19 Jun 2023 17:53:51 GMT

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!

Re: How to replace all previous values in a variable if a specific condition is met

PaigeMiller — Mon, 19 Jun 2023 18:03:17 GMT

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?

Re: How to replace all previous values in a variable if a specific condition is met

BlakeDG — Mon, 19 Jun 2023 18:08:58 GMT

No there won't be a -88, -89 etc. and Yes -87 indicates a special value and -87 would be the lowest possible value in this dataset

Re: How to replace all previous values in a variable if a specific condition is met

PaigeMiller — Mon, 19 Jun 2023 18:13:50 GMT

/* 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?

Re: How to replace all previous values in a variable if a specific condition is met

BlakeDG — Mon, 19 Jun 2023 18:21:05 GMT

That did the trick! Thank you SO much! Is there any way to also create a list in like a "proc print-like" output of the variables that were affected?

Appreciate all the help!

Re: How to replace all previous values in a variable if a specific condition is met

PaigeMiller — Mon, 19 Jun 2023 18:26:22 GMT

Data set named _MINIMUMS_ has that information. You can print it any way you want.

Re: How to replace all previous values in a variable if a specific condition is met

BlakeDG — Mon, 19 Jun 2023 18:30:40 GMT

I see that now. Thanks so much!