BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasvader
Fluorite | Level 6

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

Accepted Solutions
Ksharp
Super User

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

6 REPLIES 6
Ksharp
Super User

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
Fluorite | Level 6

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

Then I think my code fit your requirement .

sasvader
Fluorite | Level 6

I can confirm that the code you gave works.

Many thanks Keshan!

Reeza
Super User

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

BrunoSilva
Quartz | Level 8

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,

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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