topic Re: How to calculate index numbers as a new variable in SAS Programming

How to calculate index numbers as a new variable

HAHN1989 — Fri, 16 Oct 2020 08:34:30 GMT

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

Re: How to calculate index numbers as a new variable

yabwon — Fri, 16 Oct 2020 08:58:17 GMT

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

Re: How to calculate index numbers as a new variable

HAHN1989 — Mon, 19 Oct 2020 11:22:43 GMT

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;

Re: How to calculate index numbers as a new variable

yabwon — Mon, 19 Oct 2020 11:45:13 GMT

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

Re: How to calculate index numbers as a new variable

HAHN1989 — Mon, 19 Oct 2020 12:16:04 GMT

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

Re: How to calculate index numbers as a new variable

yabwon — Mon, 19 Oct 2020 16:11:40 GMT

base for 2015 - 2019:

is around 8.16 not 9.5, right?

Maybe I don't understand your request properly?

Bart

Re: How to calculate index numbers as a new variable

HAHN1989 — Tue, 20 Oct 2020 07:46:03 GMT

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

Re: How to calculate index numbers as a new variable

yabwon — Tue, 20 Oct 2020 08:02:56 GMT

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;

Re: How to calculate index numbers as a new variable

HAHN1989 — Tue, 20 Oct 2020 11:23:07 GMT

Perfect!

Thank you very muck :).

/Kasper