Hi, I'm a poor beginner who's lost in nowhere.
My situation is:
coname year ROA
company A 2007 20
company A 2008 10
company A 2009 15
compnay A 2010 9
company A 2011 1
company A 2012 -4
company A 2013 10
company B 2007 2
company B 2008 8. ... similar for company B too.
like this. I want to make comparison between company-year average of 2007-2010 and 2011-2013.
desired outcome will look like:
coname year ROA ROA_pre ROA_post
company A 2007 20 14 0
company A 2008 10 14 0
company A 2009 17 14 0
compnay A 2010 9 14 0
company A 2011 1 0 3
company A 2012 -2 0 3
company A 2013 10 0 3
company B 2007 2
company B 2008 8
Will proc summary be the answer?
Please help me. I know nothing but the fact that I know nothing...
data have;
input coname & $20. year ROA;
cards;
company A 2007 20
company A 2008 10
company A 2009 17
company A 2010 9
company A 2011 1
company A 2012 -2
company A 2013 10
company B 2007 2
company B 2008 8
;
run;
proc sql;
create table want(drop=f:) as
select *, 2007<=year<=2010 as f1, 2011<=year<=2013 as f2, mean(roa)*calculated f1 as ROA_pre, mean(roa)* calculated f2 as ROA_post
from have
group by coname,f1
order by coname, year;
quit;
How are you calculating Pre/Post?
@ESJ wrote:
Hi, I'm a poor beginner who's lost in nowhere.
My situation is:
coname year ROA
company A 2007 20
company A 2008 10
company A 2009 15
compnay A 2010 9
company A 2011 1
company A 2012 -4
company A 2013 10
company B 2007 2
company B 2008 8. ... similar for company B too.
like this. I want to make comparison between company-year average of 2007-2010 and 2011-2013.
desired outcome will look like:
coname year ROA ROA_pre ROA_post
company A 2007 20 14 0
company A 2008 10 14 0
company A 2009 17 14 0
compnay A 2010 9 14 0
company A 2011 1 0 3
company A 2012 -2 0 3
company A 2013 10 0 3
company B 2007 2
company B 2008 8
Will proc summary be the answer?
Please help me. I know nothing but the fact that I know nothing...
@ESJ wrote:
thanks for the reply... but there're more than one thousand companies in the list, and I have to compute roa_pre and roa_post differently. I don't know if proc means will help me identify all of them...
You said the logic was the year groupings? Perhaps it would help if you produced an example that's more reflective of your situation then.
You can also use an array method to calculate moving averages and then merging the data.
So first calculate your averages for the respective time periods and then merge them across.
Here's a basic example of how that works to get started:
https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas
I would do two passes - one PROC MEANS for the first years of interest and a second for the later period and then merge based on dates.
@ESJ wrote:
Hi, I'm a poor beginner who's lost in nowhere.
My situation is:
coname year ROA
company A 2007 20
company A 2008 10
company A 2009 15
compnay A 2010 9
company A 2011 1
company A 2012 -4
company A 2013 10
company B 2007 2
company B 2008 8. ... similar for company B too.
like this. I want to make comparison between company-year average of 2007-2010 and 2011-2013.
desired outcome will look like:
coname year ROA ROA_pre ROA_post
company A 2007 20 14 0
company A 2008 10 14 0
company A 2009 17 14 0
compnay A 2010 9 14 0
company A 2011 1 0 3
company A 2012 -2 0 3
company A 2013 10 0 3
company B 2007 2
company B 2008 8
Will proc summary be the answer?
Please help me. I know nothing but the fact that I know nothing...
data have;
input coname & $20. year ROA;
cards;
company A 2007 20
company A 2008 10
company A 2009 17
company A 2010 9
company A 2011 1
company A 2012 -2
company A 2013 10
company B 2007 2
company B 2008 8
;
run;
proc sql;
create table want(drop=f:) as
select *, 2007<=year<=2010 as f1, 2011<=year<=2013 as f2, mean(roa)*calculated f1 as ROA_pre, mean(roa)* calculated f2 as ROA_post
from have
group by coname,f1
order by coname, year;
quit;
I suspect the years are not consistent across all groups.
Hi @ESJ , @Reeza has a point. We trust your sample is a good representative of your real, if not we are gonna go back and forth and thread will keep getting longer. Therefore, if anything at all, we would like you provide us the best representative sample so that the solutions will make sense. Cheers!
@ESJ You're welcome. If you don't mind, Can you please mark the solution as answered and close the thread. I myself am content that I used a boolean group by expression which my professor often says I am poor at. I would like take a print of this thread and show it to him 🙂 Thank you!
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.