DATA Step, Macro, Functions and more

I want to selectively add a few data to make a new variable.

Accepted Solution Solved
Reply
Occasional Contributor ESJ
Occasional Contributor
Posts: 7
Accepted Solution

I want to selectively add a few data to make a new variable.

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


Accepted Solutions
Solution
a month ago
PROC Star
Posts: 1,831

Re: I want to selectively add a few data to make a new variable.

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


All Replies
Super User
Posts: 23,766

Re: I want to selectively add a few data to make a new variable.

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


 

Occasional Contributor ESJ
Occasional Contributor
Posts: 7

Re: I want to selectively add a few data to make a new variable.

by averaging desired years' data (2007-2010 vs. 2011-2013)
Occasional Contributor ESJ
Occasional Contributor
Posts: 7

Re: I want to selectively add a few data to make a new variable.

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...
Super User
Posts: 23,766

Re: I want to selectively add a few data to make a new variable.


@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

Occasional Contributor ESJ
Occasional Contributor
Posts: 7

Re: I want to selectively add a few data to make a new variable.

yeah... that's my fault... I'll elaborate more on the example. thanks!!
Super User
Posts: 23,766

Re: I want to selectively add a few data to make a new variable.

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


 

Solution
a month ago
PROC Star
Posts: 1,831

Re: I want to selectively add a few data to make a new variable.

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;
Super User
Posts: 23,766

Re: I want to selectively add a few data to make a new variable.

Posted in reply to novinosrin

I suspect the years are not consistent across all groups.

Occasional Contributor ESJ
Occasional Contributor
Posts: 7

Re: I want to selectively add a few data to make a new variable.

that's true... but that probably is enough for my problem. Big thanks!
Occasional Contributor ESJ
Occasional Contributor
Posts: 7

Re: I want to selectively add a few data to make a new variable.

Posted in reply to novinosrin
thank you so much! But I don't understand some syntax... let me first try it...!
PROC Star
Posts: 1,831

Re: I want to selectively add a few data to make a new variable.

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!

Occasional Contributor ESJ
Occasional Contributor
Posts: 7

Re: I want to selectively add a few data to make a new variable.

Posted in reply to novinosrin
It perfectly worked! Thank you so much!!
PROC Star
Posts: 1,831

Re: I want to selectively add a few data to make a new variable.

@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 Smiley Happy  Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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