topic creating dummy variables based on string part in SAS Data Management

creating dummy variables based on string part

ilikesas — Wed, 13 May 2015 04:29:58 GMT

hi,

suppose I have the following table:

role
CEO/CFO
CEO since 1987
President/CEO/Treasurer
Independent Director
First Independent In 2 Years

I want to create 2 new variables CEO_dummy and Independent_dummy. If for example the word CEO is present for an entry in the Role column, then the new variable CEO_dummy will have a value of 1, and 0 otherwise.

The same thing for Independent.

So the new table should look like this:

role	CEO_dummy	Independent_dummy
CEO/CFO	1	0
CEO since 1987	1	0
President/CEO/Treasurer	1	1
Independent Director	0	1
First Independent In 2 Years	0	1

Thank you!

Re: creating dummy variables based on string part

samirt — Wed, 13 May 2015 04:45:26 GMT

Suppose dataset name is dataset1

data dataset1;

set dataset1;

if find(role,'CEO','i') > 0 then CEO_dummy=1;

else Independent_dummy=0;

run;

This will solve the problem.

Re: creating dummy variables based on string part

samirt — Wed, 13 May 2015 05:07:14 GMT

Addition to the above code .This code will resolve your problem for missing dummy variables.

data dataset1;

IF FIND('role','ceo','i')>0 THEN DO;

ceo_DUMMY=1;

INDEPENDENT_DUMMY=0;

END;

ELSE IF(FIND(role','ceo','i')=0) THEN DO;

ceo_DUMMY=0;

INDEPENDENT_DUMMY=1;

END;

run;

Re: creating dummy variables based on string part

RW9 — Wed, 13 May 2015 08:55:00 GMT

Your test output doesn't work. The third line has 1 in both results, but I don't see the word independant in the string?

Its a simple task however:

data want;

set have;

if index(lowcase(role),"ceo") > 0 then ceo_dummy=1;

else ceo_dummy=0;

if index(lowcase(role),"independent") > 0 then independent=1;

else independent=0;

run;

Re: creating dummy variables based on string part

pradeepalankar — Wed, 13 May 2015 10:40:41 GMT

you may use this also:

data want;

set have;

CEO_dummy=IFN(index(upcase(role),'CEO'),1,0);

INDEPENDENT_dummy=IFN(index(upcase(role),'INDEPENDENT'),1,0);

run;

via sql:

proc sql;

create table want as

select role,

case when index(upcase(role),'CEO') then 1 else 0 end as CEO_dummy,

case when index(upcase(role),'INDEPENDENT') then 1 else 0 end as INDEPENDENT_dummy

from have;

quit;

Re: creating dummy variables based on string part

data_null__ — Wed, 13 May 2015 11:38:54 GMT

If you're searching for WORDS you need the proper function.

data dummy;
   input string $80.; 
   d1 = not not findw(string,'ceo',1,,'TSIEP');
   d2 = not not findw(string,'independent',1,,'TSIEP');
   cards; 
CEO/CFO
CEO since 1987
Independent alliaceous
Independently alliaceous
President/CEO/Treasurer
Independent Director
First Independent In 2 Years
;;;;
   run; 
proc print; 
   run;

Re: creating dummy variables based on string part

ilikesas — Wed, 13 May 2015 19:23:25 GMT

Thanks, now I even realize the importance of Independently alliaceous!