Fluorite | Level 6

## 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:

 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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

```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     SELL     2012
Firm A     SELL     2012
Firm A     SELL     2012
Firm A     SELL     2012
Firm B     SELL     2012
;
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

6 REPLIES 6
Super User

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

```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     SELL     2012
Firm A     SELL     2012
Firm A     SELL     2012
Firm A     SELL     2012
Firm B     SELL     2012
;
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

Fluorite | Level 6

## 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).

 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
Super User

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

Then I think my code fit your requirement .

Fluorite | Level 6

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

I can confirm that the code you gave works.

Many thanks Keshan!

Super User

## 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

Quartz | Level 8

## 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 = "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,

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