help with creating a new dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

help with creating a new dataset

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


Accepted Solutions
Solution
‎06-07-2014 10:38 PM
Super User
Posts: 10,023

Re: help with creating a new dataset

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

View solution in original post


All Replies
Super Contributor
Posts: 275

Re: help with creating a new dataset

proc sql;

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

quit;

Trusted Advisor
Posts: 1,228

Re: help with creating a new dataset

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;

Solution
‎06-07-2014 10:38 PM
Super User
Posts: 10,023

Re: help with creating a new dataset

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

Frequent Contributor
Posts: 96

Re: help with creating a new dataset

Thank you everyone for sharing the code.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 275 views
  • 6 likes
  • 4 in conversation