Help using Base SAS procedures

Countif with multiple conditions (Categorised firm-years)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Countif with multiple conditions (Categorised firm-years)

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:

FirmTradeYearfirmyearbuyyearsellyearmixedyear
Firm ASELL20085041
Firm ASELL20095041
Firm ASELL20105041
Firm ASELL20115041
Firm ABUY20125041
Firm ASELL20125041
Firm ABUY20125041
Firm ASELL20125041
Firm ASELL20125041
Firm ABUY20125041
Firm ABUY20125041
Firm ABUY20125041
Firm ASELL20125041
Firm BBUY20122101
Firm BBUY20122101
Firm BSELL20122101
Firm BBUY20132101
Firm BBUY20132101

Accepted Solutions
Solution
‎05-20-2015 10:28 AM
Super User
Posts: 10,018

Re: Countif with multiple conditions (Categorised firm-years)

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

View solution in original post


All Replies
Solution
‎05-20-2015 10:28 AM
Super User
Posts: 10,018

Re: Countif with multiple conditions (Categorised firm-years)

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

Occasional Contributor
Posts: 9

Re: Countif with multiple conditions (Categorised firm-years)

Hi Keshan,

Sorry for the late reply. Here is the expected output in the last three columns (buyyear, sellyear, mixedyear).

FirmTradeYearfirmyearbuyyearsellyearmixedyear
Firm ASELL20085041
Firm ASELL20095041
Firm ASELL20105041
Firm ASELL20115041
Firm ABUY20125041
Firm ASELL20125041
Firm ABUY20125041
Firm ASELL20125041
Firm ASELL20125041
Firm ABUY20125041
Firm ABUY20125041
Firm ABUY20125041
Firm ASELL20125041
Firm BBUY20122101
Firm BBUY20122101
Firm BSELL20122101
Firm BBUY20132101
Firm BBUY20132101
Super User
Posts: 10,018

Re: Countif with multiple conditions (Categorised firm-years)

Then I think my code fit your requirement .

Occasional Contributor
Posts: 9

Re: Countif with multiple conditions (Categorised firm-years)

I can confirm that the code you gave works.

Many thanks Keshan!

Super User
Posts: 19,768

Re: Countif with multiple conditions (Categorised firm-years)

I think a multilabel format plus proc freq or means would get you there. Hard to say without seeing output though

Contributor
Posts: 50

Re: Countif with multiple conditions (Categorised firm-years)

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,

🔒 This topic is solved and locked.

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

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