DATA Step, Macro, Functions and more

How to create an indicator variable

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How to create an indicator variable

I have the following table and would like an indicator variable equal to one when there is at least one competitor in the same industry in the same state in the same year. Basically it is by competition in the same industry by state. Could you please let me know the SAS SQL Proc code:

Have table:

IDYearIndustrySTATE
11989111110CA
11990111110CA
11991111110CA
11992111110CA
21989111111AR
21990111111AR
21991111111AR
31989111110CA
31990111110CA
31991111110CA
31992111110CA
31993111110CA
31994111110CA
31995111110CA
31996111110CA

 

Want table:

IDYearIndustrySTATEDUMMY
11989111110CA1
11990111110CA1
11991111110CA1
11992111110CA1
21989111111AR0
21990111111AR0
21991111111AR0
31989111110CA1
31990111110CA1
31991111110CA1
31992111110CA1
31993111110CA0
31994111110CA0
31995111110CA0
31996111110CA0

 


Accepted Solutions
Solution
‎11-27-2016 05:39 PM
Super User
Posts: 10,041

Re: How to create an indicator variable

Posted in reply to Agent1592
You mean at least TWO competitor ?


data have;
infile cards expandtabs truncover;
input ID	Year	Industry	STATE $;
cards;
1	1989	111110	CA
1	1990	111110	CA
1	1991	111110	CA
1	1992	111110	CA
2	1989	111111	AR
2	1990	111111	AR
2	1991	111111	AR
3	1989	111110	CA
3	1990	111110	CA
3	1991	111110	CA
3	1992	111110	CA
3	1993	111110	CA
3	1994	111110	CA
3	1995	111110	CA
3	1996	111110	CA
;
run;
proc sql;
select *,case when(count(distinct id) ne 1) then 1 else 0 end as flag
 from have
  group by year,industry,state
   order by id,year,industry,state;
  
quit;


View solution in original post


All Replies
Solution
‎11-27-2016 05:39 PM
Super User
Posts: 10,041

Re: How to create an indicator variable

Posted in reply to Agent1592
You mean at least TWO competitor ?


data have;
infile cards expandtabs truncover;
input ID	Year	Industry	STATE $;
cards;
1	1989	111110	CA
1	1990	111110	CA
1	1991	111110	CA
1	1992	111110	CA
2	1989	111111	AR
2	1990	111111	AR
2	1991	111111	AR
3	1989	111110	CA
3	1990	111110	CA
3	1991	111110	CA
3	1992	111110	CA
3	1993	111110	CA
3	1994	111110	CA
3	1995	111110	CA
3	1996	111110	CA
;
run;
proc sql;
select *,case when(count(distinct id) ne 1) then 1 else 0 end as flag
 from have
  group by year,industry,state
   order by id,year,industry,state;
  
quit;


Contributor
Posts: 36

Re: How to create an indicator variable

Thank you, yes I mean at least two competitors.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 306 views
  • 0 likes
  • 2 in conversation