topic Re: How to replace a blank value with -1 in SAS Programming

How to replace a blank value with -1

lapetitemaman — Fri, 25 Mar 2022 18:27:29 GMT

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;

Re: How to replace a blank value with -1

lapetitemaman — Fri, 25 Mar 2022 18:27:57 GMT

data want; set data example;
if Item105 in (' ', '.') then Item105='-1';
if Item124 in (' ', '.') then Item124='-1';

run;

Re: How to replace a blank value with -1

Astounding — Fri, 25 Mar 2022 22:18:53 GMT

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;

Re: How to replace a blank value with -1

ballardw — Fri, 25 Mar 2022 23:42:04 GMT

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;

Re: How to replace a blank value with -1

PaigeMiller — Sat, 26 Mar 2022 09:40:51 GMT

What is the point of doing this?

What can you do with '-1' that you can't do with '.'?

Re: How to replace a blank value with -1

Patrick — Sun, 27 Mar 2022 01:10:09 GMT

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.

Re: How to replace a blank value with -1

lapetitemaman — Mon, 28 Mar 2022 14:01:10 GMT

Thank you! This worked. I have tried to use array but couldn't get my code to work properly. Thanks again!

Re: How to replace a blank value with -1

lapetitemaman — Mon, 28 Mar 2022 14:03:05 GMT

These are all character variables. I am not sure why there are spaces. I am new to this forum and still learning how to post questions.

Re: How to replace a blank value with -1

lapetitemaman — Mon, 28 Mar 2022 14:03:47 GMT

I had to set up a dataset this way in order to properly run in another program.