Countif with multiple conditions (Categorised firm-years)

[sasvader]

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

[Ksharp · Accepted solution]

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

[sasvader]

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

[Ksharp]

Then I think my code fit your requirement .

[sasvader]

I can confirm that the code you gave works.

Many thanks Keshan!

[Reeza]

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

[BrunoSilva]

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,