topic Re: help with creating a new dataset in SAS Programming

help with creating a new dataset

shalmali — Sat, 07 Jun 2014 19:01:07 GMT

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

Re: help with creating a new dataset

slchen — Sat, 07 Jun 2014 20:00:56 GMT

proc sql;

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

quit;

Re: help with creating a new dataset

stat_sas — Sat, 07 Jun 2014 22:09:25 GMT

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;

Re: help with creating a new dataset

Ksharp — Sun, 08 Jun 2014 02:38:33 GMT

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

Re: help with creating a new dataset

shalmali — Sun, 08 Jun 2014 03:35:54 GMT

Thank you everyone for sharing the code.