SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

creating dummy variables based on string part

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

creating dummy variables based on string part

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:


roleCEO_dummyIndependent_dummy
CEO/CFO10
CEO since 198710
President/CEO/Treasurer11
Independent Director01
First Independent In 2 Years01



Thank you!


Accepted Solutions
Solution
‎05-13-2015 07:38 AM
Respected Advisor
Posts: 3,799

Re: creating dummy variables based on string part

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;

5-13-2015 6-36-54 AM.png

View solution in original post


All Replies
Occasional Contributor
Posts: 15

Re: creating dummy variables based on string part

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.

Occasional Contributor
Posts: 15

Re: creating dummy variables based on string part

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;

Super User
Super User
Posts: 7,977

Re: creating dummy variables based on string part

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;

Frequent Contributor
Posts: 106

Re: creating dummy variables based on string part

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;

Solution
‎05-13-2015 07:38 AM
Respected Advisor
Posts: 3,799

Re: creating dummy variables based on string part

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;

5-13-2015 6-36-54 AM.png
Super Contributor
Posts: 441

Re: creating dummy variables based on string part

Posted in reply to data_null__

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 625 views
  • 0 likes
  • 5 in conversation