grouping firms according to some criteria

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

grouping firms according to some criteria

gvkeydatadateSIC2_DigitNo_of_seg
00100431/5/1990501
00100431/5/1991501
00100431/5/1992501
00100431/5/1993501
00100431/5/1994501
00100431/5/1995501
00100431/5/1996501
00100431/5/1997501
00100431/5/1998501
00100431/5/1999353
00100431/5/1999503
00100431/5/1999503
00100431/5/2000454
00100431/5/2000354

00103631/12/1990343
00103631/12/1990353
00103631/12/1990163
00103631/12/1991163
00103631/12/1991343
00103631/12/1991353
00103631/12/1992353
00103631/12/1992343
00103631/12/1992163
00103631/12/1993163

Hi Everyone,

Sorry I'm really new to sas so please do guide me along. Your comments are much appreciated. The tables shown above consist of a part of my data. The gvkey represents a company. The 2nd column is the year. The 3rd column is the segment code(SIC2 Digit Code) for a segment of this company.  The last column represents number of segments in that company for that year. For e.g., in the bottom table, this company (001036) has 3 segments in the year 1990. the 3 segments have SIC2_Digit codes at 34,35 and 16 respectively.

QUESTION:What I would like to do is to classify the companies in each year into a focused or multi-segment (diversified) firm according to their SIC2 Digit code. The criteria is that a firm is classified as diversified if it reports more than 1 business segment with different SIC codes at the 2 digit level. Therefore, there might be some firms which have 2 segments (but same SIC2_digit) in a year, but i want to classify them as focused together with those firms with only 1 segment.

The rest of the firms are considered diversified. Does anyone has any idea as to how to tackle this question in SAS? Thank You.


Accepted Solutions
Solution
‎04-30-2012 11:01 AM
Respected Advisor
Posts: 4,646

Re: grouping firms according to some criteria

If I understand correctly, what you want, is to classify the companies according to having segments (whatever number) in more than one SIC2 codes. This would do it :

data have;
input gvkey $ datadate : ddmmyy10. SIC2_Digit No_of_seg;
format datadate ddmmyy10.;
datalines;
001004 31/5/1990 50 1
001004 31/5/1991 50 1
001004 31/5/1992 50 1
001004 31/5/1993 50 1
001004 31/5/1994 50 1
001004 31/5/1995 50 1
001004 31/5/1996 50 1
001004 31/5/1997 50 1
001004 31/5/1998 50 1
001004 31/5/1999 35 3
001004 31/5/1999 50 3
001004 31/5/1999 50 3
001004 31/5/2000 45 4
001004 31/5/2000 35 4
001036 31/12/1990 34 3
001036 31/12/1990 35 3
001036 31/12/1990 16 3
001036 31/12/1991 16 3
001036 31/12/1991 34 3
001036 31/12/1991 35 3
001036 31/12/1992 35 3
001036 31/12/1992 34 3
001036 31/12/1992 16 3
001036 31/12/1993 16 3
;

proc sql;
create table divFocus as
select gvkey, year(datadate) as year, count(distinct SIC2_digit) > 1 as diversified
from have
group by gvkey , calculated year;

PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: grouping firms according to some criteria

Can you post one sample output you are looking for? Rules of words could be interpreted many ways. BTW, the 001004 of 2000 only has 2 segments in your sample data, same thing happened with 001036 of 1993, please clarify.

Regards,

Haikuo

Occasional Contributor
Posts: 17

Re: grouping firms according to some criteria

Hi Haikuo,

My apologies for the question and I agree that it's unclear. Unfortunately I couldn't find a sample output that I'm looking for. Both the 001004 and 001036 information are correct. The data was really long so I randomly copied a few rows out of it. There are indeed 4 segments and in year 2000 for 001004 and 3 segment for 001036 in 1993.

My main idea is to create something like a dummy so i could split them into 2 different data. 0 for focus 1 for diversified, where the criteria for diversified firms as having more than 1 segment with a different sic2_digit (Therefore if a company have 2 segments but these 2 segments have the same sic2_digit, they will be classified as focused. The rest of the multi-segments firms will be diversified).

At the end of the day, I will have one pooled sample, one diversified sample and one focus sample. I hope this makes more sense! Thanks.

Solution
‎04-30-2012 11:01 AM
Respected Advisor
Posts: 4,646

Re: grouping firms according to some criteria

If I understand correctly, what you want, is to classify the companies according to having segments (whatever number) in more than one SIC2 codes. This would do it :

data have;
input gvkey $ datadate : ddmmyy10. SIC2_Digit No_of_seg;
format datadate ddmmyy10.;
datalines;
001004 31/5/1990 50 1
001004 31/5/1991 50 1
001004 31/5/1992 50 1
001004 31/5/1993 50 1
001004 31/5/1994 50 1
001004 31/5/1995 50 1
001004 31/5/1996 50 1
001004 31/5/1997 50 1
001004 31/5/1998 50 1
001004 31/5/1999 35 3
001004 31/5/1999 50 3
001004 31/5/1999 50 3
001004 31/5/2000 45 4
001004 31/5/2000 35 4
001036 31/12/1990 34 3
001036 31/12/1990 35 3
001036 31/12/1990 16 3
001036 31/12/1991 16 3
001036 31/12/1991 34 3
001036 31/12/1991 35 3
001036 31/12/1992 35 3
001036 31/12/1992 34 3
001036 31/12/1992 16 3
001036 31/12/1993 16 3
;

proc sql;
create table divFocus as
select gvkey, year(datadate) as year, count(distinct SIC2_digit) > 1 as diversified
from have
group by gvkey , calculated year;

PG

PG
Occasional Contributor
Posts: 17

Re: grouping firms according to some criteria

thank you!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 316 views
  • 0 likes
  • 3 in conversation