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 |
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
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 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.
The first year the company adopted the program, regardless of the product. I updated my post with the expected outcome. Thank you.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.