Counting observations with an 'if'

[Pepijn Peters]

Hello all,

For a large sample of firms, I need to count the number of (1) business segments and (2) geographic segments the business is in for the years 2010-2019. Using Ben Earnhart's code (https://www.statalist.org/forums/for...variable-group); bysort fyear CUSIP: gen howmany=_N gives me the number of segments for every firm in a particular year.

Now this is good, but I have to separate the business segments from the geographic segments.

For a particular firm, this is what it looks like:

SegmentType CompanyName fyear howmany
GEOSEG NEW SOURCE ENERGY PRTRS LP 2013 7
GEOSEG NEW SOURCE ENERGY PRTRS LP 2013 7
BUSSEG NEW SOURCE ENERGY PRTRS LP 2013 7
BUSSEG NEW SOURCE ENERGY PRTRS LP 2013 7
GEOSEG NEW SOURCE ENERGY PRTRS LP 2013 7
BUSSEG NEW SOURCE ENERGY PRTRS LP 2013 7
BUSSEG NEW SOURCE ENERGY PRTRS LP 2013 7
GEOSEG NEW SOURCE ENERGY PRTRS LP 2014 8
BUSSEG NEW SOURCE ENERGY PRTRS LP 2014 8
GEOSEG NEW SOURCE ENERGY PRTRS LP 2014 8
GEOSEG NEW SOURCE ENERGY PRTRS LP 2014 8
BUSSEG NEW SOURCE ENERGY PRTRS LP 2014 8
BUSSEG NEW SOURCE ENERGY PRTRS LP 2014 8
BUSSEG NEW SOURCE ENERGY PRTRS LP 2014 8
BUSSEG NEW SOURCE ENERGY PRTRS LP 2014 8

Anyone knows how to distinguish in the code between the two types of segments?

[Nick Cox]

At a guess

 bysort fyear CUSIP SegmentType: gen howmany2 = _N

[Pepijn Peters]

Hi Nick,

This works. Thank you! Now I have the count for BUSSEG and GEOSEG in every row, but do you also know how I change the dataset so that I have one row for every year-firm combination (with two columns added: one for the number of business segments, and one for the number of geographical segments)?

[Nick Cox]

contract fyear CUSIP SegmentType 
reshape wide _freq, i(CUSIP fyear) j(SegmentType) string

[Pepijn Peters]

Wow... It works!

Thanks a lot.

[Ikogor Clay]

reshape wide command revealed that a variable that should be same is not the same value within same hhid . example variable typeofroof not constant within hhid,

since all household members should have same type of roof,

Please is there a command I can use to correct the errors?

Perhaps so that the value of typeofroof for the first housemember listed in the household is used to be same for all household member.

Thanks for your help.