Hello,
Maybe it's a relatively simpel question, but it has been been a bit difficult for me. The issue is that I want to calculate an "index" in a new variable, which measures
changes against a base value in simplified fashion, following the dataset.
The new column should be like;
index_number = (year of AWSSI/ base year (mean 2015-19))* 100
Each year (2008-19) of the data is subsequently normalized against the base year (2015-19) in the same fashion in a new column.
I hope anyone can help
All best,
Kasper
Ahhh, sorry, stupid me!
I calculated the denominator wrong. Updated version of the code below.
Bart
data have;
input year AWSSI;
cards;
2008 1
2009 2
2010 3
2011 4
2012 5
2013 6
2014 7
2015 8
2016 9
2017 10
2018 11
2019 11
;
run;
data want;
if _N_ = 1 then
do;
do until(eof);
set have end = eof;
where year between 2015 and 2019;
base + AWSSI;
N + 1;
end;
base = base / N;
end;
set have;
index_number = (AWSSI/ base)* 100;
index_number_rounded = round((AWSSI/ base)* 100);
output;
run;
Hi,
do you mean something like this:
data have;
do year = 2008 to 2019;
AWSSI = 100+100*ranuni(123);
output;
end;
run;
data want;
if _N_ = 1 then
do;
do until(eof);
set have end = eof;
where year between 2015 and 2019;
base + AWSSI;
_N_ + 1;
end;
base = base / _N_;
end;
set have;
index_number = (AWSSI/ base)* 100;
output;
run;
?
Bart
Thank you very much @yabwon,
After adjusting your code to my dataset it work perfectly, but the "base" is unfortunately calculated wrongly both in your example and when I apply it to my data (the mean 2015-19). Why is this the case?
Following this statements (codes):
data want;
if _N_ = 1 then
do;
do until(eof);
set awssi_year end = eof;
where year base + between 2015 and 2019;
base + AWSII;
_N_ + 1;
end;
base = base/_N_;
end;
set Awssi_year;
index_number = (AWSSI/ base)* 100;
output;
run;
Hi,
Two things:
1) Could you share data in a form that I could use it to? it will be easier. To make it simple let's assume the dataset looks like this:
data have;
input year AWSSI;
cards;
2008 1
2009 2
2010 3
2011 4
2012 5
2013 6
2014 7
2015 8
2016 9
2017 10
2018 11
2019 11
;
run;
What would the expected base be?
2) This line of code doesn't seems to be right:
where year base + between 2015 and 2019;
Bart
Hi Bart
Thank you for your quick reply.
In your example the base would be the mean of 2015-19.
Base (9.5) = (8+9+10+11)/4
The new column should be like;
index_number = (number/ base(mean 2015-19))* 100
Each year (2008-19) of the data is subsequently normalized against the base year (2015-19) to measure the relative variation from the base.
All best/ Kasper
base for 2015 - 2019:
2015 8
2016 9
2017 10
2018 11
2019 11
is around 8.16 not 9.5, right?
Maybe I don't understand your request properly?
Bart
Sorry for the misunderstanding.
In your data example the "base value - 2015-19" would be (8+9+10+11+11)= 49 divide by the number of observations (5) = 9.8 - mean value.
This value would be the reference year.
Example; The first year (1 (observation year) / 9.8 (basevalue) ) * 100 = 10 rounded
In the following tabel I have illustrated the intended (desired column).
/Kasper
Ahhh, sorry, stupid me!
I calculated the denominator wrong. Updated version of the code below.
Bart
data have;
input year AWSSI;
cards;
2008 1
2009 2
2010 3
2011 4
2012 5
2013 6
2014 7
2015 8
2016 9
2017 10
2018 11
2019 11
;
run;
data want;
if _N_ = 1 then
do;
do until(eof);
set have end = eof;
where year between 2015 and 2019;
base + AWSSI;
N + 1;
end;
base = base / N;
end;
set have;
index_number = (AWSSI/ base)* 100;
index_number_rounded = round((AWSSI/ base)* 100);
output;
run;
Perfect!
Thank you very muck :).
/Kasper
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.