Hello, fellow SAS users,
I have a large dataset with many blank values. I need to replace the blank values with -1. I use the following code but it only changes one variable at a time. I am wondering if someone could help me out with this. Thank you so much!
data example;
input ID Item105 $ Item124 $ Item192 $ Item020 $ Item 005 $ Item 041 $ Item225 $ Item308 $;
datalines;
001 A . B 1 . 0 C A
002 B D A 0 0 . . C
003 . A C . . 1 B .
004 C C . . 1 0 A C
005 B . D 0 0 1 . .
;
data want;
input ID Item105 $ Item124 $ Item192 $ Item020 $ Item 005 $ Item 041 $ Item225 $ Item308 $;
datalines;
001 A -1 B 1 -1 0 C A
002 B D A 0 0 -1 -1 C
003 -1 A C -1 -1 1 B -1
004 C C -1 -1 1 0 A C
005 B -1 D 0 0 1 -1 -1
;
This code works but I have over 1000 variables.
data want; set data example;
if Item105 in (' ', '.') then Item105='-1';
if Item124 in (' ', '.') then Item105='-1';
run;
When you intend to process many variables in exactly the same way, the right tool for the job is an array. For example:
data want;
set example;
array items {*} item: ;
do _n_=1 to dim(items)
if items{_n_} in (' ', '.') then items{_n_} = ' ';
end;
run;
When you intend to process many variables in exactly the same way, the right tool for the job is an array. For example:
data want;
set example;
array items {*} item: ;
do _n_=1 to dim(items)
if items{_n_} in (' ', '.') then items{_n_} = ' ';
end;
run;
What is special about the value -1 in your later steps?
It is also a bit suspect that all of your of your variables are character.
Your data step as posted does not run because you have spaces between "item" and the number for a couple variables on the Input statement.
I might be that a format is the simplest to display that value without changing the original values though likely not if some of your values are much longer than a couple characters.
proc format; value $dashone ' ' = '-1' ; run; data example; input ID Item105 $ Item124 $ Item192 $ Item020 $ Item005 $ Item041 $ Item225 $ Item308 $; datalines; 001 A . B 1 . 0 C A 002 B D A 0 0 . . C 003 . A C . . 1 B . 004 C C . . 1 0 A C 005 B . D 0 0 1 . . ; proc print data=example; format _character_ $dashone.; run;
If this is about actually changing the values from missing to -1 then using an array as already shown is likely what you need to do. But as @PaigeMiller writes: What is the point of doing this? What does -1 allow you to do that a missing doesn't.
If it's simply about reporting and "looking" at the data then consider to use a format instead that you then apply to all the variables.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.