BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
BlakeDG
Calcite | Level 5

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
/* 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?

--
Paige Miller

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
BlakeDG
Calcite | Level 5
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
PaigeMiller
Diamond | Level 26
/* 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?

--
Paige Miller
BlakeDG
Calcite | Level 5
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!
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

How to Concatenate Values

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 243 views
  • 0 likes
  • 2 in conversation