topic Re: Iterative Match within a period in New SAS User

Iterative Match within a period

dapenDaniel — Mon, 30 Sep 2019 19:46:31 GMT

Hello,

I have a dataset. It records the products that companies produce each year and the components for each product ("Categories" column is only for convenience and it is not in my real dataset). All the components of each firm in five years consist of a component base. It is updated as firms introduce more products. The file has been sorted by FirmID, Beginyear and components.

If all the components of a new product do not overlap with the component base in five years, it will be considered as radical products. For example, Firm 10002 introduced KJ128F in 1996 and its components were Z14 and T32, which do not exist in the component base between 1991 and 1995 within Firm 10002. Thus KJ128F is a radical product.

If one of the components of a new product overlaps with the component base in five years, it will be considered as incremental products. For example, Firm 10002 introduced OP284L in 1995 and its component was X48. X48 exists in the component base between 1990 and 1994. So OP284L is an incremental product.

I would like to know how many incremental products and radical products that each firm has for each year. The output that I want is below. In case you need the file, I also attached the excel file.

Have data:

FirmID	ProductID	BeginYear	Components	Categories
10001	AA386G	1992	B30	radical
10001	AA386G	1992	V55
10001	AA386G	1992	T92
10001	AB259T	1993	R26	Incremental
10001	AB259T	1993	V55
10001	AB259T	1993	U38
10001	AC449P	1993	Y38	radical
10001	AC449P	1993	I92
10002	XV429S	1990	E33	radical
10002	CT746B	1991	X48	radical
10002	CT746B	1991	K93
10002	CT746B	1991	O28
10002	RU239F	1992	X48	Incremental
10002	RU239F	1992	Q15
10002	DH469E	1993	K93	Incremental
10002	DH469E	1993	Q15
10002	MH387T	1994	O28	Incremental
10002	OP284L	1995	X48	Incremental
10002	KJ128F	1996	Z14	Radical
10002	KJ128F	1996	T32
10003	CN726Q	1991	E55	radical
10003	SZ116R	1992	E55	Incremental
10003	SZ116R	1992	W87
10003	TU692E	1992	W87	Incremental
10003	TU692E	1992	I92

want data

FirmID	BeginYear	nb_Incremental	nb_Radical
10001	1992	0	1
10001	1993	1	1
10002	1990	0	1
10002	1991	0	1
10002	1992	1	0
10002	1993	1	0
10002	1994	1	0
10002	1995	1	0
10002	1996	0	1
10003	1991	0	1
10003	1992	2	0

Re: Iterative Match within a period

Reeza — Mon, 30 Sep 2019 21:35:42 GMT

Isn't the same as your previous questions, but you're limiting the data to 5 years? Or am I missing something?

Re: Iterative Match within a period

dapenDaniel — Mon, 30 Sep 2019 22:03:23 GMT

Yes. You are correct but I do not know how to revise the code offered by Ksharp to limit the data to 5 years. The link is for the previous question.

https://communities.sas.com/t5/New-SAS-User/Iterative-Match/m-p/591821#M15276

Re: Iterative Match within a period

Reeza — Mon, 30 Sep 2019 22:08:25 GMT

So your real question is how to limit years. Can you explain how the 5 years are filtered? Is it a specific 5 years? Is it relative to a date variable?

Re: Iterative Match within a period

dapenDaniel — Tue, 01 Oct 2019 01:36:21 GMT

Thank you, @Reeza ! It is like a rolling window. For example, if a product was introduced in 2000, I would like to know whether the components of this product exist in the component base from 1995 to 1999. If a product was introduced in 2003, I would like to know whether the components exist in the component base from 1998 to 2002. Does that make sense?