Hi All,
I have a dataset with the following information: Ticker, Year, and COUNT. I want to create a new subset of this data where I keep firms whose COUNT is not zero for any years in the sample. For instance my original dataset is as follows:
Ticker Year COUNT
A 2007 0
A 2008 0
A 2009 1
A 2010 1
A 2011 3
A 2012 0
B 2007 1
B 2008 3
B 2009 2
B 2010 1
B 2011 1
B 2012 1
From the above example, I want to create a new subset which will have information only for firm B because B did not have COUNT = 0 for any of the years.
Ticker Year COUNT
B 2007 1
B 2008 3
B 2009 2
B 2010 1
B 2011 1
B 2012 1
I would appreciate if someone would help me in getting this desired output.
Thank you for your time.
Shalmali
data have; input Ticker $ Year COUNT ; cards; A 2007 0 A 2008 0 A 2009 1 A 2010 1 A 2011 3 A 2012 0 B 2007 1 B 2008 3 B 2009 2 B 2010 1 B 2011 1 B 2012 1 ; run; proc sql; create table want as select * from have group by ticker having sum(count=0) eq 0; quit;
Xia Keshan
proc sql;
select * from have group by ticker having min(count)^=0 order by year;
quit;
proc sql;
select distinct quote(trim(ticker)) into :firm separated by ',' from have where count=0;
select * from have where ticker not in (&firm);
quit;
data have; input Ticker $ Year COUNT ; cards; A 2007 0 A 2008 0 A 2009 1 A 2010 1 A 2011 3 A 2012 0 B 2007 1 B 2008 3 B 2009 2 B 2010 1 B 2011 1 B 2012 1 ; run; proc sql; create table want as select * from have group by ticker having sum(count=0) eq 0; quit;
Xia Keshan
Thank you everyone for sharing the code.
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.