topic Re: creating column with Changing formula in SAS Programming

creating column with Changing formula

soumri — Sat, 22 Jul 2017 22:31:34 GMT

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.

Re: creating column with Changing formula

Reeza — Sat, 22 Jul 2017 23:11:34 GMT

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?

Re: creating column with Changing formula

soumri — Sat, 22 Jul 2017 23:40:14 GMT

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.

Re: creating column with Changing formula

Shmuel — Sat, 22 Jul 2017 23:37:18 GMT

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?

Re: creating column with Changing formula

soumri — Sat, 22 Jul 2017 23:47:28 GMT

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.

Re: creating column with Changing formula

Reeza — Sun, 23 Jul 2017 00:09:55 GMT

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.

Re: creating column with Changing formula

Shmuel — Sun, 23 Jul 2017 00:13:13 GMT

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?

Re: creating column with Changing formula

Patrick — Sun, 23 Jul 2017 03:41:07 GMT

@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;

Re: creating column with Changing formula

soumri — Sun, 23 Jul 2017 09:46:48 GMT

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.

Re: creating column with Changing formula

soumri — Sun, 23 Jul 2017 11:44:29 GMT

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.

Re: creating column with Changing formula

soumri — Sun, 23 Jul 2017 12:02:40 GMT

@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...

Re: creating column with Changing formula

Shmuel — Sun, 23 Jul 2017 18:45:14 GMT

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;