Module Output

SQL: [IDI_Community].[pent_age].age_YYYYMM
SAS: libname cmbp ODBC dsn=idi_community_srvprd schema=pent_age; proc print data = cmbp.age_YYYYMM; run ;
How to access a code module in the Data Lab : Read here

Context:

This module creates a table of permanent enterprises with the date of birth. The birth of a business or enterprise is usually the date when they legally begin operation. This can depend on the trading status of the business, i.e. sole trader, partnership or corporation. For limited liability companies, this is the date of incorporation. For limited partnerships, it is the date of registration. For sole proprietorships, where there is no legal separation between the individual and the business, the distinction is less clear. In practice, tax registrations and records are used to identify when a firm has been born.

Operational Definition

In the LBD, firm birth dates are recorded in the Longitudinal Business Frame (LBF), derived from the Business Register (BR). The firm population in the BR is updated monthly, based primarily on IR’s Client Register. Businesses are required to register for GST if they earn more than $60,000 per year. Firms provide IR with an industry classification, which is edited for certain known issues by Stats NZ. Firms can also register for a New Zealand Business Number, and some legal structures (eg LLCs) require registration with the Companies Office. These sources are not currently used to identify new firm births in the BR. Companies Office information is used (alongside IR data) to identify ceases, and to identify changes in business structure (eg, changes to parent-subsidiary structures based on shareholding information) and legal name changes.

The birth date listed in the BR refers to either the income (GST activity or IR10 income) start date or employment start date, whichever is earlier. Therefore, the BR birth date in some cases could be earlier than the date an enterprise becomes economically significant and later than the date it was registered with IR.

In some cases, recorded birth dates are out of sync with observed activity at the firm. For example, PAYE and GST records may show that the firm was employing, or otherwise active prior to their recorded birth date. For firms born after the start of the LBF, researchers generally make adjustments to firm age to account for this activity (using either employment data, GST data, or both as appropriate for their research question).

Birth dates in the BR are recorded at the level of the Enterprise (legal unit). Events such as a change in legal structure can lead to breaks in Enterprise number identifiers. Fabling (2011) develops a method to repair enterprise identifier breaks, based on observed employment by creating Permanent Enterprise numbers (PENTs). The code snippet below provides an example to collect firm birth dates at the PENT level, adjusting for observed activity.

Key Notes

1. There is a large spike in recorded birth dates on 1 October 1986. This reflects the introduction of GST in New Zealand, requiring many small firms to register with IR.
2. Adjustments to recorded birth years to allow for observed activity prior to a firm’s recorded birth also lead to spikes in apparent birth year in the first years for which employment is available. Depending on the question at hand, researchers may find it useful to top-code firm age (ie, to use a category such as >15 years) to allow for these spikes.
3. Births are recorded in the LBF as a date variable. Specific dates can be attributed to either a calendar month or year, or a firm’s financial year, depending on the research question.

References & Contacts

1. Fabling, R (2011). Keeping it Together: Tracking Firms in New Zealand’s Longitudinal Business Database. Motu Working Paper 11-01. March 2011.

Community of Interest

Parameters

The following parameters should be supplied to this module to run it in the database:

1. IDI_Sandpit: The SQL database on which the module dataset is to be created.
2. {targetschema}: The project schema under the target database into which the module dataset is to be created.
3. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name.
4. {lbdcleanversion}: The LBD refresh where the module will be based upon, e.g. “ibuldd_clean_archive_202112”.
5. {auxdbsrc}: Auxillary database, typically the source of the labour tables, not expected to change but parameterised in case, e.g. “ibuldd_research_datalab”.
6. {penttbldate}: Version date of the labour tables to be used in YYYYMMDD, e.g. 202111020
7. {module}: The name of this module, e.g. “pent_age”

Dependencies

[ibuldd_clean].[dbo].[fact_lbf_enterprise_year]
[idi_adhoc].[clean_read_ir].[pent_idi_$(penttbldate)_rfabling]
ibuldd_clean.[dbo].[pent_pbn_month_l_idi_$(penttbldate)_rfabling]
ibuldd_clean.[dbo].[pent_month_gst_idi_$(penttbldate)_rfabling] 

Outputs

{targetdb}.{targetschema}.pent_age

Variable Descriptions

Module Version & Change History

Code

:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar penttbldate "{penttbldate}"
:setvar module "{module}"

/* Assign the target database to which all the components need to be created in. */
USE $(targetdb);

/* Delete the database object if it already exists */
DROP TABLE IF EXISTS [$(targetdb)].[$(targetschema)].[$(module)];
GO

;WITH pent_core as (
select 
pent.pent 
,min(pent.start_month) as [start_date]
,max(pent.end_month) as [end_date]
from [IDI_Adhoc].[clean_read_IR].[pent_IDI_$(penttbldate)_rfabling] pent
/* minimum birth date of a enterprise_nbr */
left join [ibuldd_clean].[dbo].[fact_lbf_enterprise_year] lbf on pent.enterprise_nbr=lbf.enterprise_nbr
group by pent.pent
)

,lbf_birth as (
	select 
	pent 
	,min(isnull(lbf_birth_yearmonth,999912)) as lbf_birth
	from (
	SELECT
	pent.pent 
	,format(lbf.birth_date, 'yyyyMM') as lbf_birth_yearmonth
	from [IDI_Adhoc].[clean_read_IR].[pent_IDI_$(penttbldate)_rfabling] pent
	left join [ibuldd_clean].[dbo].[fact_lbf_enterprise_year] lbf on pent.enterprise_nbr=lbf.enterprise_nbr
	) as ym
	group by pent
	)

	, first_employment as (
	select 
	pent.pent 
	,min(isnull(L.dim_month_key,999912)) as first_employment
	from [IDI_Adhoc].[clean_read_IR].[pent_IDI_$(penttbldate)_rfabling] pent
	left join [ibuldd_clean].[dbo].[pent_pbn_month_L_IDI_$(penttbldate)_rfabling] L on pent.pent=L.pent 
	group by pent.pent
	)

	,first_gst as (
	select 
	pent.pent 
	,min(isnull(GST.dim_month_key,999912)) as first_gst
	from [IDI_Adhoc].[clean_read_IR].[pent_IDI_$(penttbldate)_rfabling] pent
	left join [ibuldd_clean].[dbo].[pent_month_GST_IDI_$(penttbldate)_rfabling] GST on pent.pent=GST.pent 
	group by pent.pent
	)
	
	,min_measure as (
	select 
	pent.pent 
	,pent.[start_date]
	,pent.[end_date]
	,bir.[lbf_birth]
	,emp.[first_employment]
	,gst.[first_gst]
	,isnull([measure_value],999912) as earliest_data_year
	from pent_core pent
	left join [lbf_birth] bir on pent.pent=bir.pent
	left join [first_employment] emp on pent.pent=emp.pent
	left join [first_gst] gst on pent.pent=gst.pent
	cross apply (
	select
	min(first_year) as measure_value 
	from (values 
	([lbf_birth])
	,([first_employment])
	,([first_gst])
	) as A(first_year)
	) A		
	)

	
	select 
	*
	 into [$(targetdb)].[$(targetschema)].[$(module)]
	from min_measure;