Hello,
I want to calculate 5-year average on a time series dataset with Years as the variable names. How do I correctly input the variable names in my code?
My data:
Company | 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 |
A | 9470 | 6920 | 3540 | 8247 | 8177 | 8001 | 7047 | 5829 | 5388 | 6491 |
B | 8141 | 8511 | 6755 | 7427 | 6252 | 6551 | 8634 | 6489 | 5715 | 7338 |
C | 506 | 90 | 1073 | 183 | 217 | 1432 | 699 | 382 | 253 | 340 |
D | 1027 | 1521 | 1319 | 390 | 667 | 1101 | 371 | 1201 | 273 | 1311 |
What I want
Company | 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2001-2005 | 2006-2010 |
A | 9470 | 6920 | 3540 | 8247 | 8177 | 8001 | 7047 | 5829 | 5388 | 6491 | 7270.8 | 6551.2 |
B | 8141 | 8511 | 6755 | 7427 | 6252 | 6551 | 8634 | 6489 | 5715 | 7338 | 7417.2 | 6945.4 |
C | 506 | 90 | 1073 | 183 | 217 | 1432 | 699 | 382 | 253 | 340 | 413.8 | 621.2 |
D | 1027 | 1521 | 1319 | 390 | 667 | 1101 | 371 | 1201 | 273 | 1311 | 984.8 | 851.4 |
Thank you,
Data want;
set have;
'2001-2005'n=mean(of 2001-2005);
'2006-2010'n=mean(of 2006-2010);
run;
Don't. Use variable labels instead that can be exported instead of names but programming with variable names like that is a recipe for making silly mistakes like adding two raw values together because you forgot the quotes. It's really hard to find those errors.
If you set the following, SAS will import the data with valid SAS names.
options validvarname = V7;
If you have to stick with this, for whatever reason, use the same notation you used on the right hand side of the equal sign.
data want;
set have;
Mean_2001_2005 = mean(of '2001'n-'2005'n);
label mean_2001_2005 = "Mean: 2001 to 2005";
run;
proc print data=want LABELS;
run;
@michokwu wrote:
Hello,
I want to calculate 5-year average on a time series dataset with Years as the variable names. How do I correctly input the variable names in my code?
My data:
Company 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 A 9470 6920 3540 8247 8177 8001 7047 5829 5388 6491 B 8141 8511 6755 7427 6252 6551 8634 6489 5715 7338 C 506 90 1073 183 217 1432 699 382 253 340 D 1027 1521 1319 390 667 1101 371 1201 273 1311
What I want
Company 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2001-2005 2006-2010 A 9470 6920 3540 8247 8177 8001 7047 5829 5388 6491 7270.8 6551.2 B 8141 8511 6755 7427 6252 6551 8634 6489 5715 7338 7417.2 6945.4 C 506 90 1073 183 217 1432 699 382 253 340 413.8 621.2 D 1027 1521 1319 390 667 1101 371 1201 273 1311 984.8 851.4
Thank you,
Data want; set have; '2001-2005'n=mean(of 2001-2005); '2006-2010'n=mean(of 2006-2010); run;
Don't. Use variable labels instead that can be exported instead of names but programming with variable names like that is a recipe for making silly mistakes like adding two raw values together because you forgot the quotes. It's really hard to find those errors.
If you set the following, SAS will import the data with valid SAS names.
options validvarname = V7;
If you have to stick with this, for whatever reason, use the same notation you used on the right hand side of the equal sign.
data want;
set have;
Mean_2001_2005 = mean(of '2001'n-'2005'n);
label mean_2001_2005 = "Mean: 2001 to 2005";
run;
proc print data=want LABELS;
run;
@michokwu wrote:
Hello,
I want to calculate 5-year average on a time series dataset with Years as the variable names. How do I correctly input the variable names in my code?
My data:
Company 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 A 9470 6920 3540 8247 8177 8001 7047 5829 5388 6491 B 8141 8511 6755 7427 6252 6551 8634 6489 5715 7338 C 506 90 1073 183 217 1432 699 382 253 340 D 1027 1521 1319 390 667 1101 371 1201 273 1311
What I want
Company 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2001-2005 2006-2010 A 9470 6920 3540 8247 8177 8001 7047 5829 5388 6491 7270.8 6551.2 B 8141 8511 6755 7427 6252 6551 8634 6489 5715 7338 7417.2 6945.4 C 506 90 1073 183 217 1432 699 382 253 340 413.8 621.2 D 1027 1521 1319 390 667 1101 371 1201 273 1311 984.8 851.4
Thank you,
Data want; set have; '2001-2005'n=mean(of 2001-2005); '2006-2010'n=mean(of 2006-2010); run;
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.