BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ESJ
Calcite | Level 5 ESJ
Calcite | Level 5

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...

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

13 REPLIES 13
Reeza
Super User

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
Calcite | Level 5 ESJ
Calcite | Level 5
by averaging desired years' data (2007-2010 vs. 2011-2013)
ESJ
Calcite | Level 5 ESJ
Calcite | Level 5
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...
Reeza
Super User

@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.

http://support.sas.com/kb/25/027.html

ESJ
Calcite | Level 5 ESJ
Calcite | Level 5
yeah... that's my fault... I'll elaborate more on the example. thanks!!
Reeza
Super User

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...


 

novinosrin
Tourmaline | Level 20
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;
Reeza
Super User

I suspect the years are not consistent across all groups.

ESJ
Calcite | Level 5 ESJ
Calcite | Level 5
that's true... but that probably is enough for my problem. Big thanks!
ESJ
Calcite | Level 5 ESJ
Calcite | Level 5
thank you so much! But I don't understand some syntax... let me first try it...!
novinosrin
Tourmaline | Level 20

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
Calcite | Level 5 ESJ
Calcite | Level 5
It perfectly worked! Thank you so much!!
novinosrin
Tourmaline | Level 20

@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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 789 views
  • 2 likes
  • 3 in conversation