BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KyleM_Corrie
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
KyleM_Corrie
Fluorite | Level 6

Thanks for the suggestions, guys.

 

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

 

OPTIONS MISSING=0;

View solution in original post

6 REPLIES 6
kannand
Lapis Lazuli | Level 10

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...!!!

Kannan Deivasigamani
stat_sas
Ammonite | Level 13

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
Fluorite | Level 6

Thanks for the suggestions, guys.

 

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

 

OPTIONS MISSING=0;

kannand
Lapis Lazuli | Level 10

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

 

 

Kannan Deivasigamani
PGStats
Opal | Level 21

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;
PG
data_null__
Jade | Level 19
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;

Capture.PNG

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1287 views
  • 2 likes
  • 5 in conversation