How to create an indicator variable

[Agent1592]

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

[Ksharp · Accepted solution]

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;

[Agent1592]

Thank you, yes I mean at least two competitors.