Hi all, would appreciate some help with this problem.
I'm trying to find out the number of firm-years for the entire sample according to three categories: buy firm-year, sell firm-year, and mixed firm-year.
The firm-years are categorized based on the second column. If a firm-year only has BUY, it'll be a buy firm-year. If a firm-year has both BUYs and SELLs, it'll be a mixed-firm year.
For example Firm A would have a total of 5 firm-years comprising of 4 sell firm-years and 1 mixed firm-year.
I used this code below to obtain the total firm-years but I'm having difficulty breaking them down into the categories.
proc sql;
create table TABLE as
select *,count(distinct year) as firmyear
from DATA
group by FIRM;
quit;
Is there a SAS equivalent of Excel's countif function where I can set multiple conditions?
Example of the sample:
Firm | Trade | Year | firmyear | buyyear | sellyear | mixedyear |
Firm A | SELL | 2008 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2009 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2010 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2011 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2012 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2012 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2012 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2012 | 5 | 0 | 4 | 1 |
Firm B | BUY | 2012 | 2 | 1 | 0 | 1 |
Firm B | BUY | 2012 | 2 | 1 | 0 | 1 |
Firm B | SELL | 2012 | 2 | 1 | 0 | 1 |
Firm B | BUY | 2013 | 2 | 1 | 0 | 1 |
Firm B | BUY | 2013 | 2 | 1 | 0 | 1 |
Where is your output ?
data DATA; infile cards expandtabs truncover; input Firm & $10. Trade $ Year ; cards; Firm A SELL 2008 Firm A SELL 2009 Firm A SELL 2010 Firm A SELL 2011 Firm A BUY 2012 Firm A SELL 2012 Firm A BUY 2012 Firm A SELL 2012 Firm A SELL 2012 Firm A BUY 2012 Firm A BUY 2012 Firm A BUY 2012 Firm A SELL 2012 Firm B BUY 2012 Firm B BUY 2012 Firm B SELL 2012 Firm B BUY 2013 Firm B BUY 2013 ; run; proc sql; create table TABLE as select *,(select count(distinct year)from DATA where Firm=a.Firm ) as firmyear, (select count(distinct year) from ( select year from DATA where Firm=a.Firm group by FIRM,Year having sum(Trade='SELL')=0 )) as buyyear, (select count(distinct year) from ( select year from DATA where Firm=a.Firm group by FIRM,Year having sum(Trade='BUY')=0 )) as sellyear, (select count(distinct year) from ( select year from DATA where Firm=a.Firm group by FIRM,Year having sum(Trade='BUY') ne 0 and sum(Trade='SELL') ne 0 )) as mixedyear from DATA as a; quit;
Xia Keshan
Where is your output ?
data DATA; infile cards expandtabs truncover; input Firm & $10. Trade $ Year ; cards; Firm A SELL 2008 Firm A SELL 2009 Firm A SELL 2010 Firm A SELL 2011 Firm A BUY 2012 Firm A SELL 2012 Firm A BUY 2012 Firm A SELL 2012 Firm A SELL 2012 Firm A BUY 2012 Firm A BUY 2012 Firm A BUY 2012 Firm A SELL 2012 Firm B BUY 2012 Firm B BUY 2012 Firm B SELL 2012 Firm B BUY 2013 Firm B BUY 2013 ; run; proc sql; create table TABLE as select *,(select count(distinct year)from DATA where Firm=a.Firm ) as firmyear, (select count(distinct year) from ( select year from DATA where Firm=a.Firm group by FIRM,Year having sum(Trade='SELL')=0 )) as buyyear, (select count(distinct year) from ( select year from DATA where Firm=a.Firm group by FIRM,Year having sum(Trade='BUY')=0 )) as sellyear, (select count(distinct year) from ( select year from DATA where Firm=a.Firm group by FIRM,Year having sum(Trade='BUY') ne 0 and sum(Trade='SELL') ne 0 )) as mixedyear from DATA as a; quit;
Xia Keshan
Hi Keshan,
Sorry for the late reply. Here is the expected output in the last three columns (buyyear, sellyear, mixedyear).
Firm | Trade | Year | firmyear | buyyear | sellyear | mixedyear |
Firm A | SELL | 2008 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2009 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2010 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2011 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2012 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2012 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2012 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | BUY | 2012 | 5 | 0 | 4 | 1 |
Firm A | SELL | 2012 | 5 | 0 | 4 | 1 |
Firm B | BUY | 2012 | 2 | 1 | 0 | 1 |
Firm B | BUY | 2012 | 2 | 1 | 0 | 1 |
Firm B | SELL | 2012 | 2 | 1 | 0 | 1 |
Firm B | BUY | 2013 | 2 | 1 | 0 | 1 |
Firm B | BUY | 2013 | 2 | 1 | 0 | 1 |
Then I think my code fit your requirement .
I can confirm that the code you gave works.
Many thanks Keshan!
I think a multilabel format plus proc freq or means would get you there. Hard to say without seeing output though
Hello,
Using the table provided by Xia Keshan.
You can use CASE WHEN.
proc sql;
select *,
COUNT(distinct year) as firmyear,
COUNT(distinct CASE WHEN TRADE = "BUY" then Year else . end) as buyyear,
COUNT(distinct CASE WHEN TRADE = "SELL" then Year else . end) as sellyear
from data
group by firm;
quit;
But for the mixed year i think is not possible using this.
Best regards,
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: