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

Solved
Occasional Contributor
Posts: 9

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

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

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

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

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

Super User
Posts: 10,784

## 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: 23,771

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

## 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
• 3043 views
• 4 likes
• 4 in conversation