DATA Step, Macro, Functions and more

creating column with Changing formula

Reply
Contributor
Posts: 55

creating column with Changing formula

Hi everyone
I have a database of the form:

 

Effect    fact1  fact2 fact3 fact4 fact5           time     val1   val2   val3  val4

HP11      0.1    -0.2   0.1    0.1    -0.3              1      0.01  0.02  0.05  0.08

HP12      0.2    -0.3   0.3    0.4     0.1              2      0.02  0.03  0.02  0.08

HP13      0.1     0.2   0.3    0.4     0.5              3      0.07  0.02  0.03  0.03

HP21      0.3    -0.4   0.7    0.2    -0.3              4      0.02  0.02  0.04  0.08

HP22      0.1    -0.7   0.2    0.2    -0.2              5      0.04  0.07  0.01  0.08

HP23      0.2     0.2   0.2    0.2     0.5              6      0.01  0.07  0.01  0.02

HP32      0.1     0.5   0.1    0.1     0.2              7      0.02  0.05  0.02  0.07

HP34      0.1     0.3   0.1    0.1     0.3              8      0.06  0.02  0.02  0.04

HP35      0.7    -0.1   0.6    0.3     0.1              9      0.01  0.06  0.02  0.08

SP11      0.3    -0.4   0.7    0.7    -0.3            10      0.03  0.06  0.06  0.08

SP12      0.1     0.2   0.2    0.2    -0.1            11      0.02  0.04  0.05  0.08

SP13      0.9    -0.1   0.5    0.1     0.3            12      0.02  0.01  0.05  0.08

SP21      0.4    -0.1   0.7    0.1    -0.3            13      0.07  0.01  0.04  0.08

SP22      0.5    -0.2   0.1    0.7    -0.3            14      0.09  0.01  0.03  0.02

SP23      0.1    -0.2   0.1    0.3    -0.2            15      0.02  0.06  0.02  0.08

SP32      0.6    -0.2   0.5    0.3    -0.1            16      0.01  0.07  0.03  0.04

SP34      0.7    -0.4   0.5    0.2     0.3            17      0.02  0.09  0.06  0.02

SP35      0.9    -0.2   0.1    0.1     0.1            18      0.02  0.02  0.02  0.02

.              .          .       .         .        .               19      0.08  0.01  0.07  0.03

.              .          .       .         .        .               20      0.06  0.03  0.02  0.02

.              .          .       .         .        .               21      0.01  0.02  0.03  0.05

.              .          .       .         .        .               22      0.06  0.04  0.01  0.06

.              .          .       .         .        .               23      0.02  0.04  0.02  0.05 

.              .          .       .         .        .               24      0.04  0.01  0.05  0.09

.              .          .       .         .        .               25      0.01  0.01  0.03  0.01

For each effect I want to create a column with its name in which I apply the following formula in each line:
fact1+  (fact2* val1)+(fact3* val2)+(fact4*val3)+ (fact5*val4)
This formula is repeated on each line under the following conditions:
Fact1, fact2, fact3, fact4 and fact5 do not change and always have the same values of the same effect.
Val1 val2 val3 and val4 change to each line from Time = 1 to time = n.

I give the result (what i want) for the first effect for the three first lines of my example:

 

Effect    fact1  fact2 fact3 fact4 fact5           time     val1   val2   val3  val4    HP11

HP11      0.1    -0.2   0.1    0.1    -0.3              1      0.01  0.02  0.05  0.08     0.081  [=0.1+(-0.2*0.01)+(0.1*0.02)+(0.1*0.05)+(-0.3*0.08)]

HP12      0.2    -0.3   0.3    0.4     0.1              2      0.02  0.03  0.02  0.08     0.077  [=0.1+(-0.2*0.02)+(0.1*0.03)+(0.1*0.02)+(-0.3*0.08)]

HP13      0.1     0.2   0.3    0.4     0.5              3      0.07  0.02  0.03  0.03     0.082  [=0.1+(-0.2*0.07)+(0.1*0.02)+(0.1*0.03)+(-0.3*0.03)]

This should be repeated with all the effects from Time equal to 1 until the last value of Time.
Thank you for helping me.

Super User
Posts: 17,819

Re: creating column with Changing formula

Why does HP12 start with 0.2? Why doesn't it use the fact1 value. It may easier to follow if you write out the formula's for the first three lines. 

 

How does the formula shown not work in code? What does your code look like so far?

Contributor
Posts: 55

Re: creating column with Changing formula

[ Edited ]

In my database the variable effect is like Id's, each Id has its own values of fact1, fact2, fact3, fact4 and fact5.
The values of the variables val1, val2, val3 and val4 can be considered as an independent database to use to apply the formula.
I gave the first 3 results for the effect HP11 and I mentioned the formulas before each result between two brackets.

The results for HP12 will appear in an independent column as for HP11 and results will start with 0.2.

Trusted Advisor
Posts: 1,375

Re: creating column with Changing formula

Did you tried code:

data want;
  set have;
       new_var = fact1+  (fact2* val1)+(fact3* val2)+(fact4*val3)+ (fact5*val4);
run;

Why have you titled the calculated variable as HP11? 

Contributor
Posts: 55

Re: creating column with Changing formula

Thank you for answering me, but it is not what I want.
The result looks in excel lake:

        HP11 = fact $ 1 + (fact $ 2 * val1) + (fact $ 3 * val2) + (fact $ 4 * val3) + (fact $ 5 * val4);
Repeat from Time = 1 to Time = n;

of course for every effect.

 

Super User
Posts: 17,819

Re: creating column with Changing formula

The calculation appears straightforward and @Shmuel answer is exactly what I would suggest, so I think at this point you need to more clearly explain what you have, what you need, and very specifically, what you need help with because that's unclear. 

Trusted Advisor
Posts: 1,375

Re: creating column with Changing formula

[ Edited ]

How many variables do you want to create per set of facts? - is it n (number of times given) ?

 

For effect = X you cannot create more than one variable named X !

so, how should you name the variable calculated with values of time=2, of time=3, etc?

Contributor
Posts: 55

Re: creating column with Changing formula

For each effect it is a calculation of row * column repeated n times without in each case modifying the values of fact1 .... Fact5 relating to this effect. Only the values of val1 ... val4 which will be modified each time.
The results are mentioned in a new column which bears the name of this effect.
@Patrick answered me somewhat but I want the results to be put side by side vertically.
thank you.

Respected Advisor
Posts: 3,890

Re: creating column with Changing formula

[ Edited ]

@soumri

Given the previous discussion I'm assuming you're after something like below.

For future questions: Please provide the source data in your question as SAS data steps as I've done it now for you in below code for tables factors and horizons.

data factors;
input Effect $ fact1 fact2 fact3 fact4 fact5;
datalines;
HP11 0.1 -0.2 0.1 0.1 -0.3 
HP12 0.2 -0.3 0.3 0.4 0.1 
HP13 0.1 0.2 0.3 0.4 0.5 
HP21 0.3 -0.4 0.7 0.2 -0.3 
HP22 0.1 -0.7 0.2 0.2 -0.2 
HP23 0.2 0.2 0.2 0.2 0.5 
HP32 0.1 0.5 0.1 0.1 0.2 
HP34 0.1 0.3 0.1 0.1 0.3 
HP35 0.7 -0.1 0.6 0.3 0.1 
SP11 0.3 -0.4 0.7 0.7 -0.3 
SP12 0.1 0.2 0.2 0.2 -0.1 
SP13 0.9 -0.1 0.5 0.1 0.3 
SP21 0.4 -0.1 0.7 0.1 -0.3 
SP22 0.5 -0.2 0.1 0.7 -0.3 
SP23 0.1 -0.2 0.1 0.3 -0.2 
SP32 0.6 -0.2 0.5 0.3 -0.1 
SP34 0.7 -0.4 0.5 0.2 0.3 
SP35 0.9 -0.2 0.1 0.1 0.1 
;
run;

data horizons;
  input time val1 val2 val3 val4;
  datalines;
1 0.01 0.02 0.05 0.08
2 0.02 0.03 0.02 0.08
3 0.07 0.02 0.03 0.03
4 0.02 0.02 0.04 0.08
5 0.04 0.07 0.01 0.08
6 0.01 0.07 0.01 0.02
7 0.02 0.05 0.02 0.07
8 0.06 0.02 0.02 0.04
9 0.01 0.06 0.02 0.08
10 0.03 0.06 0.06 0.08
11 0.02 0.04 0.05 0.08
12 0.02 0.01 0.05 0.08
13 0.07 0.01 0.04 0.08
14 0.09 0.01 0.03 0.02
15 0.02 0.06 0.02 0.08
16 0.01 0.07 0.03 0.04
17 0.02 0.09 0.06 0.02
18 0.02 0.02 0.02 0.02
19 0.08 0.01 0.07 0.03
20 0.06 0.03 0.02 0.02
21 0.01 0.02 0.03 0.05
22 0.06 0.04 0.01 0.06
23 0.02 0.04 0.02 0.05 
24 0.04 0.01 0.05 0.09
25 0.01 0.01 0.03 0.01
;
run;

data want;
  set factors;
  do _point=1 to _nobs;
    set horizons nobs=_nobs point=_point;
    target_var = fact1+  (fact2* val1)+(fact3* val2)+(fact4*val3)+ (fact5*val4);
    output;
  end;
run;

 

Or as a SQL:

proc sql;
  create table want2 as
    select 
      f.*
      ,h.*
      ,f.fact1+  (f.fact2* h.val1)+(f.fact3* h.val2)+(f.fact4*h.val3)+ (f.fact5*h.val4) as target_var
    from 
      factors as f, horizons as h
  ;
quit;

 

Contributor
Posts: 55

Re: creating column with Changing formula

Yes @Patrick, point of view cacula, that's right. I can even mention as it is resolved, but is there a way to have the results for each effect in column and not online of the form target_var1 target_var2 target_var3 .... for each effect.

Contributor
Posts: 55

Re: creating column with Changing formula

[ Edited ]

@Patrick,

I want that results appear with this form:

target_var1   target_var2....

target_var1 for effect 1 (HP11)

target_var2 for effect 2 (HP12)

etc...

Trusted Advisor
Posts: 1,375

Re: creating column with Changing formula

Next code is based on @Patrick code with sligh changes in steps 2 and 3

I hope this will fit to what you want:

data factors;
input Effect $ fact1 fact2 fact3 fact4 fact5;
datalines;
HP11 0.1 -0.2 0.1 0.1 -0.3 
HP12 0.2 -0.3 0.3 0.4 0.1 
HP13 0.1 0.2 0.3 0.4 0.5 
HP21 0.3 -0.4 0.7 0.2 -0.3 
HP22 0.1 -0.7 0.2 0.2 -0.2 
HP23 0.2 0.2 0.2 0.2 0.5 
HP32 0.1 0.5 0.1 0.1 0.2 
HP34 0.1 0.3 0.1 0.1 0.3 
HP35 0.7 -0.1 0.6 0.3 0.1 
SP11 0.3 -0.4 0.7 0.7 -0.3 
SP12 0.1 0.2 0.2 0.2 -0.1 
SP13 0.9 -0.1 0.5 0.1 0.3 
SP21 0.4 -0.1 0.7 0.1 -0.3 
SP22 0.5 -0.2 0.1 0.7 -0.3 
SP23 0.1 -0.2 0.1 0.3 -0.2 
SP32 0.6 -0.2 0.5 0.3 -0.1 
SP34 0.7 -0.4 0.5 0.2 0.3 
SP35 0.9 -0.2 0.1 0.1 0.1 
;
run;

data horizons;
  retain n; drop n;
  infile datalines truncover;
  input time val1 val2 val3 val4; 
  n+1;
  if time=99 then call symput('_nobs',left(n-1));
  else output;
datalines;
1 0.01 0.02 0.05 0.08
2 0.02 0.03 0.02 0.08
3 0.07 0.02 0.03 0.03
4 0.02 0.02 0.04 0.08
5 0.04 0.07 0.01 0.08
6 0.01 0.07 0.01 0.02
7 0.02 0.05 0.02 0.07
8 0.06 0.02 0.02 0.04
9 0.01 0.06 0.02 0.08
10 0.03 0.06 0.06 0.08
11 0.02 0.04 0.05 0.08
12 0.02 0.01 0.05 0.08
13 0.07 0.01 0.04 0.08
14 0.09 0.01 0.03 0.02
15 0.02 0.06 0.02 0.08
16 0.01 0.07 0.03 0.04
17 0.02 0.09 0.06 0.02
18 0.02 0.02 0.02 0.02
19 0.08 0.01 0.07 0.03
20 0.06 0.03 0.02 0.02
21 0.01 0.02 0.03 0.05
22 0.06 0.04 0.01 0.06
23 0.02 0.04 0.02 0.05 
24 0.04 0.01 0.05 0.09
25 0.01 0.01 0.03 0.01
99
;
run;
%put No of Values = &_nobs;

data want;
  set factors;
  retain tar1-tar&_nobs;
  array tar {&_nobs} tar1-tar&_nobs;
  do _point=1 to &_nobs;
    set horizons point=_point;
    tar(time) = fact1+  (fact2* val1)+(fact3* val2)+(fact4*val3)+ (fact5*val4);
  end;
  drop time val1-val4;
  output; 
run;
Ask a Question
Discussion stats
  • 11 replies
  • 277 views
  • 0 likes
  • 4 in conversation