Creating dummy using proc sql

Accepted Solution Solved
Reply
Regular Contributor
Posts: 183
Accepted Solution

Creating dummy using proc sql

I have 16 sectors in my dataset. I need to create dummy for each sector. It's very long if I write the code as follows:

data x;

set y;

if sector = 'Chemicals, rubber, plastics, non-metallic products' then Chemicals = 1; else Chemicals = 0;

if sector = 'Construction' then Construction = 1; else Construction = 0;

if sector = 'Education, Health' then Education = 1; else Education = 0;

:

:

:

run;

Can I write the code shortly by using proc sql?


Accepted Solutions
Solution
‎10-24-2014 06:51 PM
Respected Advisor
Posts: 3,790

Re: Creating dummy using proc sql

data company;
   infile cards dsd;
  
input company_code :$12.  sector &$50.;
  
cards;
AN8068571086,Primary sector  
ANN6748L1027,Machinery, equipment, furniture, recycling
BMG0129K1045,Other services
BMG029951016,Other services
BMG0464B1072,Other services
BMG0692U1099,Other services
BMG100821401,Primary sector
BMG169621056,Food, beverages, tobacco
BMG200452024,Other services
BMG303971060,Other services
;;;;
   run;
proc print;
  
run;
proc summary data=company nway;
  
class sector;
   output out=sectorCode(rename=(_level_=SectorCode) drop=_type_ _freq_ index=(sector)) / levels;
  
run;
data company;
   set company;
   set sectorcode key=sector/unique;
  
run;
proc print;
  
run;

10-24-2014 5-49-07 PM.png

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: Creating dummy using proc sql

Seems to me that Array could be one of the solutions. The example can be expanded to adapt the number of variables that you have in your real data.

data x;

     set y;

     array dummy(3) Chemicals Construction Education;

     do i=1 to dim (dummy);

           dummy(i)=find(sector,vname(dummy(i)), 'i');

     end;

run;

Good luck,

Haikuo

Regular Contributor
Posts: 183

Re: Creating dummy using proc sql

Thanks. When I ran the code then one variable named i is also created and the value for i is 4 for all observations. What is this i variable?

Respected Advisor
Posts: 3,156

Re: Creating dummy using proc sql

that was the index for Array. just drop it.

...

...

drop i;

run;

Regular Contributor
Posts: 183

Re: Creating dummy using proc sql

Thanks.

Regular Contributor
Posts: 183

Re: Creating dummy using proc sql

Hi, one more question. If I want to group the sectors such as 1, 2, 3, ......, then what will be the code? For example, Chemicals will be 1, Construction will be 2, Education will be 3, ...........

Trusted Advisor
Posts: 1,800

Re: Creating dummy using proc sql

PROC GLMMOD does exactly what you want.

Regular Contributor
Posts: 183

Re: Creating dummy using proc sql

Code you please write the code?

Respected Advisor
Posts: 2,655

Re: Creating dummy using proc sql

proc glmmod data=yourdata outparm=parm outdesign=design;

class sector;

model y=sector;

run;

See the SAS/STAT documentation for PROC GLMMOD for examples and definitions of the datasets created.

Steve Denham

Regular Contributor
Posts: 183

Re: Creating dummy using proc sql

It's not working. Let me explain about my dataset. I have two variables in my dataset: company_code and sector. Say, company_100 is in chemicals sector, Company_200 is in Metal sector, ..... etc. Chemical sector will be 1, Metal sector will be 2, ..... etc.

Super User
Posts: 19,194

Re: Creating dummy using proc sql

Sample data and output required would help a lot.

Regular Contributor
Posts: 183

Re: Creating dummy using proc sql

The following is the sample of my dataset:

Company_code     sector

AN8068571086Primary sector
ANN6748L1027Machinery, equipment, furniture, recycling
BMG0129K1045Other services
BMG029951016Other services
BMG0464B1072Other services
BMG0692U1099Other services
BMG100821401Primary sector
BMG169621056Food, beverages, tobacco

I want to put sector code for each sector. Say, primary sector will be 1, machinery, equipment, furniture, recycling will be 2, ..... and so on.

Super User
Posts: 19,194

Re: Creating dummy using proc sql

Sample output?

Regular Contributor
Posts: 183

Re: Creating dummy using proc sql

In the output, one column will have to be created named sector_code. Output should be look like this;

company_code     sector                                                     sector_code

AN8068571086Primary sector11
ANN6748L1027Machinery, equipment, furniture, recycling7
BMG0129K1045Other services9
BMG029951016Other services9
BMG0464B1072Other services9
BMG0692U1099Other services9
BMG100821401Primary sector11
BMG169621056Food, beverages, tobacco4
BMG200452024Other services9
BMG303971060Other services9
Super User
Posts: 19,194

Re: Creating dummy using proc sql

That's different than your original question, its a standard recode with if/then statements.

You can also use proc format if you want.

See this paper using the first example.

🔒 This topic is solved and locked.

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

Discussion stats
  • 28 replies
  • 1605 views
  • 8 likes
  • 7 in conversation