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
Use SQL
proc sql;
create table want as
select *, max(SP500) as SP500_SIC
from have
group by SIC;
quit;
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.
Use SQL
proc sql;
create table want as
select *, max(SP500) as SP500_SIC
from have
group by SIC;
quit;
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;
Thank you guys for the prompt reply.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.