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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.