help with creating a new dataset

[shalmali]

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

[slchen]

proc sql;

select * from have group by ticker having min(count)^=0 order by year;          

quit;

[stat_sas]

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;

[Ksharp · Accepted solution]

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

[shalmali]

Thank you everyone for sharing the code.