How to create an indicator variable

Solved
Frequent Contributor
Posts: 76

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:

 ID Year Industry STATE 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

Want table:

 ID Year Industry STATE DUMMY 1 1989 111110 CA 1 1 1990 111110 CA 1 1 1991 111110 CA 1 1 1992 111110 CA 1 2 1989 111111 AR 0 2 1990 111111 AR 0 2 1991 111111 AR 0 3 1989 111110 CA 1 3 1990 111110 CA 1 3 1991 111110 CA 1 3 1992 111110 CA 1 3 1993 111110 CA 0 3 1994 111110 CA 0 3 1995 111110 CA 0 3 1996 111110 CA 0

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

Re: How to create an indicator variable

```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;

```

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

Re: How to create an indicator variable

```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;

```
Frequent Contributor
Posts: 76

Re: How to create an indicator variable

Thank you, yes I mean at least two competitors.

☑ This topic is solved.