Element wise operations in SAS EG

[madara155]

Hi,

I was a Matlab user happened to stuck in SAS at new work place.

I have some data in SAS (big file with many rows and columns) and need to do few basic maths operations on them.

As shown in above image, I have columns A to E as a SAS data file and need to calculate columns F to G (required equations are written in the image - hope it can be clearly seen).

It is just typing one formula in Excel and dragging it to all the other cells. But given the volume of data and the rest of the processing, want to do this in SAS and I can't seem to find a way to do it.

Can someone please help me to get this done?

THANKS HEAPS.

[PaigeMiller]

In SAS, we don't refer to columns or cells as you do in Excel. You will use the variable name in SAS, which you do not show.

So, show us your SAS data set.

[PeterClemmensen]

One way (with part of your posted data)

data have;
input year regionA regionB regionC regionD;
datalines;
2002  0.67  0.36  0.8  -0.48 
2003  0.9  -0.56 -0.47  0.4  
2004  0.46  0.37  0.16  0.73 
;

data want;
   set have;
   array r region:;
   array c ChangeIndexA ChangeIndexB ChangeIndexC ChangeIndexD;
   array l[4] _temporary_ (1 1 1 1);

   do over r;
      if _N_ = 1 then c = l[_i_];
      else do;
         c = l[_i_] * (1 + r / 100);
         l[_i_] = c;
      end;
   end;

   format change: 8.3;

run;

Result:

regionA regionB regionC regionD ChangeIndexA ChangeIndexB ChangeIndexC ChangeIndexD
0.67    0.36    0.8    -0.48    1.000        1.000        1.000        1.000
0.9    -0.56   -0.47    0.4     1.009        0.994        0.995        1.004
0.46    0.37    0.16    0.73    1.014        0.998        0.997        1.011
........

[ballardw · Accepted solution]

Best is to provide example data that you have in the form of a data step, or barring that plain text. It is impossible to code against data in a picture.

Here is an example of 1) small data step to create a few lines of data and 2) implement the desired calculations.

data have;
   input year delta_1 delta_2 delta_3 delta_4;
datalines;
2002 0.67 -0.36 0.8 -0.48
2003 0.9 -0.56  -0.47 0.4
2004 0.46 0.37 0.16  0.73
;

data want;
   set have;
   array d (*) delta_1- delta_4;
   array c (4) change_1-Change_4;
   retain change_1-change_4;
   if _n_=1 then do i=1 to dim(c);
      c[i]=1;
   end;
   else do i= 1 to dim(c);
      c[i] = c[i] *(1+ d[i]/100);
   end;
run;

Without the names of your variables I used the above with 1 corresponding to A, 2 to B and so on. SAS has some features like variable lists that work much better with numeric suffixes that will not work with character suffixes.

The core of the code comes with 4 pieces:

1) Arrays are quite often the preferred approach when you are doing identical (or nearly so) operations to multiple variables

2) Retain will keep values of a variable across iterations of the data step boundary

3) SAS has an automatic data step variable _n_ that is the number of times the data step executes. In simple code, such as this, it can be used to count the number of records processed. So when the first set of values from the Have data set are read the value is 1. So that can be used to set the initial values.

The assignment like this:

c[i] = c[i] *(1+ d[i]/100);

is saying "set the new value of the referenced array element to the existing (retained from previous iteration) times the delta value.

Since the delta and change arrays are, if done properly, aligned for the 1,2,3,4 (A,B,C,D) versions then all 4 get calculated.

Nice thing about arrays. If you have 25 Delta variables and they are nicely named you only need to change the Delta_4 to Delta_25 and make the C array go to 25 as well and the code will work.

Some cautions: If any of the Delta values are missing then the expression (1+d[i]/100) becomes missing so the c[i] * (a missing value) will be missing. Meaning all of the remaining changes from that point on will be since the missing value would be retained.

Second there is no specific format assigned so SAS will typically default to BEST12. You can assign a format such a 8.2 to just show 2 decimal places but there will be more decimal values carried in calculations.

[madara155]

Hi

Thanks for the detailed reply.

Your method works perfectly. 👍

[Tom]

But what is the MEANING of the formula?

Are you just doing compounded interest calculations?

data have ;
  input year interest ;
cards;
2002 0.0067
2003 0.0090
2004 0.0046
;

data want ;
   set have;
   retain value 1 ;
   output;
   value = value + value*interest;
run;

proc print;
run;

Obs    year    interest     value

 1     2002      .0067     1.00000
 2     2003      .0090     1.00670
 3     2004      .0046     1.01576

PS Please do not post PHOTOGRAPHS of data.  There is no way to write a program to convert the photograph into a dataset.