BookmarkSubscribeRSS Feed
soumri
Quartz | Level 8

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.

11 REPLIES 11
Reeza
Super User

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?

soumri
Quartz | Level 8

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.

Shmuel
Garnet | Level 18

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? 

soumri
Quartz | Level 8

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.

 

Reeza
Super User

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. 

Shmuel
Garnet | Level 18

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?

soumri
Quartz | Level 8

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.

Patrick
Opal | Level 21

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

 

soumri
Quartz | Level 8

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.

soumri
Quartz | Level 8

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

Shmuel
Garnet | Level 18

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1344 views
  • 0 likes
  • 4 in conversation