topic Re: Countif with multiple conditions (Categorised firm-years) in SAS Procedures

Countif with multiple conditions (Categorised firm-years)

sasvader — Wed, 20 May 2015 05:57:27 GMT

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

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

Ksharp — Wed, 20 May 2015 14:28:31 GMT

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

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

Reeza — Wed, 20 May 2015 15:53:17 GMT

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

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

BrunoSilva — Thu, 21 May 2015 08:31:34 GMT

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,

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

sasvader — Sat, 23 May 2015 13:38:57 GMT

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

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

Ksharp — Tue, 26 May 2015 12:18:04 GMT

Then I think my code fit your requirement .

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

sasvader — Wed, 27 May 2015 10:57:05 GMT

I can confirm that the code you gave works.

Many thanks Keshan!