DATA Step, Macro, Functions and more

SELECT Statement / Assigning Values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

SELECT Statement / Assigning Values

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.


Accepted Solutions
Solution
‎10-31-2015 09:38 PM
Occasional Contributor
Posts: 12

Re: SELECT Statement / Assigning Values

Thanks for the suggestions, guys.

 

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

 

OPTIONS MISSING=0;

View solution in original post


All Replies
Regular Contributor
Posts: 161

Re: SELECT Statement / Assigning Values

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
Trusted Advisor
Posts: 1,204

Re: SELECT Statement / Assigning Values

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;

 

Solution
‎10-31-2015 09:38 PM
Occasional Contributor
Posts: 12

Re: SELECT Statement / Assigning Values

Thanks for the suggestions, guys.

 

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

 

OPTIONS MISSING=0;

Regular Contributor
Posts: 161

Re: SELECT Statement / Assigning Values

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
Respected Advisor
Posts: 4,644

Re: SELECT Statement / Assigning Values

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
Respected Advisor
Posts: 3,777

Re: SELECT Statement / Assigning Values

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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