help with creating a dummy variable

[shalmali]

Hi All,

I have a quick question about creating a variable. I have a dataset with the following information: (This data is sorted by SIC)

Firm Year      SIC      SP500

A     2001     1211     0

A     2002     1211     0

B     2001     1211     1

C     2003     1211     1

S     2001     2525     0

T     2002     2525     0

U     2002     2525     0

I want to create a new variable SP500_SIC that takes value 1 if SP500 is 1 even once for that SIC. In other words, I want the output to be like:

Firm Year      SIC      SP500     SP500_SIC

A     2001     1211     0               1

A     2002     1211     0               1

B     2001     1211     1               1

C     2003     1211     1               1

S     2001     2525     0               0

T     2002     2525     0               0

U     2002     2525     0               0

Thank you

[PaigeMiller]

Using PROC MEANS or PROC SUMMARY, determine the maximum value of SP500 for each SIC. This will be SP500_SIC

Next step: merge the output of PROC MEANS in with the original data.

[Reeza · Accepted solution]

Use SQL

proc sql;

create table want as

select *, max(SP500) as SP500_SIC

from have

group by SIC;

quit;

[NickR]

Assuming SP500 is a numeric variable.

proc sql;

  create table outds as select *, max(sp500) as sp500_SIC from inds group by sic order by sic,firm;

quit;

[shalmali]

Thank you guys for the prompt reply.