Create Positive and Negative Trends

[michokwu]

Hello,

I'd like to see how a program's pre- and post-implementation trends compare. I've having difficulties correctly creating the positive and negative values. Please see sample data below (Trend should be 0, if the program was implemented in 2012, +1 for every year after, -1 for every year before)

data biz_trend;
set biz_data;
by business adoption_year;
retain Trend;
if first.business and first.adoption_year then Trend=0;
else Trend=Trend+1;
run;

Year	Business	Products	Adoption_year
2010	Company A
2011	Company A
2012	Company A	convenience	2012
2013	Company A
2014	Company A	shopping	2014
2014	Company A	raw materials	2014
2015	Company A
2016	Company A
2017	Company A	major equipment	2017
2010	Company B
2011	Company B
2012	Company B
2013	Company B
2014	Company B
2015	Company B
2016	Company B	raw materials	2016
2017	Company B	convenience	2017
2010	Company C
2011	Company C
2012	Company C
2013	Company C
2014	Company C	convenience	2014
2015	Company C
2016	Company C	major equipment	2016
2017	Company C	convenience	2017

What I want

Year	Business	Adoption_year	Trend
2010	Company A		-2
2011	Company A		-1
2012	Company A	2012	0
2013	Company A		1
2014	Company A	2014	2
2014	Company A	2014	3
2015	Company A		4
2016	Company A		5
2017	Company A	2017	6
2010	Company B		-6
2011	Company B		-5
2012	Company B		-4
2013	Company B		-3
2014	Company B		-2
2015	Company B		-1
2016	Company B	2016	0
2017	Company B	2017	1
2010	Company C		-4
2011	Company C		-3
2012	Company C		-2
2013	Company C		-1
2014	Company C	2014	0
2015	Company C		1
2016	Company C	2016	2
2017	Company C	2017	3

[ballardw]

What role does the Products variable play in this process? Since Company A shows 4 different adoption years depending on the Products then you need to show what you expect as the actual output of the process.

[michokwu]

The Product variable is not relevant for this purpose. Thank you.

[ballardw]

---

@michokwu wrote:
The Product variable is not relevant for this purpose. Thank you.

---

So which adoption year is supposed to be used for each Company? We need a rule to select the correct one when there are multiple adoptions years.

[michokwu]

The first year the company adopted the program, regardless of the product. I updated my post with the expected outcome. Thank you.

[maguiremq · Accepted solution]

I feel like you have a typo in your 'want' data set. I think this is what you meant, but I may have missed a key detail in your post. I'm mainly wondering about Company A's two values for 2014--is this intentionally incremented by 1 even though it's the same distance from the adoption year?

proc sql;
	select
				a.year,
				a.business,
				a.adoption_year,
				a.year - b.first_year as trend
	from
				have as a
					left join
				(select
							business,
							min(adoption_year) as first_year
				 from
				 			have
				 where
				 			adoption_year is not null
				 group by
				 			business) as b
				 		on a.business = b.business
	order by
				a.business, a.year;
quit;

Year 	Business 	Adoption_year 	trend
2010 	Company A 	. 	-2
2011 	Company A 	. 	-1
2012 	Company A 	2012 	0
2013 	Company A 	. 	1
2014 	Company A 	2014 	2
2014 	Company A 	2014 	2
2015 	Company A 	. 	3
2016 	Company A 	. 	4
2017 	Company A 	2017 	5
2010 	Company B 	. 	-6
2011 	Company B 	. 	-5
2012 	Company B 	. 	-4
2013 	Company B 	. 	-3
2014 	Company B 	. 	-2
2015 	Company B 	. 	-1
2016 	Company B 	2016 	0
2017 	Company B 	2017 	1
2010 	Company C 	. 	-4
2011 	Company C 	. 	-3
2012 	Company C 	. 	-2
2013 	Company C 	. 	-1
2014 	Company C 	2014 	0
2015 	Company C 	. 	1
2016 	Company C 	2016 	2
2017 	Company C 	2017 	3

[michokwu]

You're right. Its a typo. Thank you very much!