SELECT Statement / Assigning Values

[KyleM_Corrie]

Hello,

I was wondering if there were some remedy for my issue. I have a data set, and I am creating new variables via an assignment statement based on the values of another variable from a select statement. However, when I do so, I'm left with missing variables in all of the other categories, and I'd like them to be assigned a value of zero instead.

DATA HAVE;
INPUT RACE1;
DATALINES;
1
2
3
4
5
6
7
8
9
;
RUN;

DATA HAVE2;
SET HAVE;
SELECT (RACE1);
 WHEN (1) WHITE=1;
 WHEN (2) BLACK=1;
 WHEN (3,4,5) NATIVE=1;
 WHEN (6,7) ASIAN=1;
 WHEN (8,9) OTHER=1;
END;
RUN;

Is there any way to accomplish this with a select statement? I'm trying to avoid a complicated series of IF-THE/ELSE statements, and I've also done it with an ARRAY and DO loop combo, but I'm curious if there's just some simple I could use as part of this code to assign zeroes to all my missing values.

Thanks.

[kannand]

COALESCE function using SQL as shown below will take care of your issue...

DATA HAVE;
INPUT RACE1;
DATALINES;
1
2
3
4
5
6
7
8
9
;
RUN;
 
DATA HAVE2;
SET HAVE;
SELECT (RACE1);
 WHEN (1) WHITE=1;
 WHEN (2) BLACK=1;
 WHEN (3,4,5) NATIVE=1;
 WHEN (6,7) ASIAN=1;
 WHEN (8,9) OTHER=1;
END;
RUN;
proc sql; 
     create table have2 as select race1,
     coalesce(white,0),coalesce(black,0),coalesce(native,0),coalesce(asian,0),coalesce(other,0)
     from have2;
quit;

Good Luck...!!!

[stat_sas]

An easy way to do the same:

proc stdize data=have2 out=want reponly missing=0;
run;

It will replace missing values with 0 for all numerical variables in the dataset have2. If you want specific variables for this treatment, just add var statement in above syntax

proc stdize data=have2 out=want reponly missing=0;

var white black; /* this will repace missing with 0 in white and black variables only */

run;

[KyleM_Corrie · Accepted solution]

Thanks for the suggestions, guys.

I think the easiest way was to add a system option.

OPTIONS MISSING=0;

[kannand]

It might be worthwhile to note that the OPTIONS MISSING= just changes the way data is displayed but does not internally change the value.... please refer to 

http://www.sascommunity.org/wiki/Tips:Convert_MISSING_to_0

[PGStats]

If you actually want your variables to take values 0 or 1, I think the simplest way is:

DATA HAVE2;
SET HAVE;
WHITE = RACE1 IN (1);
BLACK = RACE1 IN (2);
NATIVE = RACE1 IN (3, 4, 5);
ASIAN = RACE1 IN (6, 7);
OTHER = RACE1 IN (8, 9);
RUN;

[data_null__]

proc format;
   value race 1='White' 2='Black' 3,4,5='Natice' 6,7='Asian' 8,9='Other';
   run;
DATA HAVE;
   do race1=1 to 9;
      output;
      end;
   RUN;
proc transreg cprefix=0;;
   model class(race1/zero=none);
   format race1 race.;
   output design out=dummies(drop=_: int:);
   run;
proc print;
   run;