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.
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?
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.
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?
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.
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.
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?
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.
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;
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.
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...
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.