Search code examples
sasmultiplication

Multiply each line of a file by all lines of another file using SAS

I have 2 databases:

Database 1

Database 1

Database 2

Database 2

I need to multiply each line of database 1 by all the lines of database 2 (ie. line 1 of database 1 by all lines of database 2; line 2 of database 1 by all lines of database 2, etc), in such a way:

Example equations

![Example equations ]3

I need to get a value for each stage within each id.
Can you help me with this, please? I use SAS software.

Solution

  • I am not going to retype all of the data from your pictures but here is a program that will work to two of your "stages". So the first dataset I called HAVE and the second one I called STAGES and this data step will generate a WANT dataset that keeps all of the data from HAVE and adds the new calculated variables.

    data want ;
  set have ;
  array vars x y z ;
  array stages a b ;
  do p=1 to dim(stages);
    set stages point=p ;
    array factor m1-m3 ;
    stages(p)=0;
    do j=1 to dim(vars);
      stages(p) + vars(j)*factor(j) ;
    end;
  end;
  drop stage m1-m3 j;
run;

    So here is the result for two rows of input data and two of the new stages.

    Obs    id     x      y      z       a       b
 1      1    0.5    0.5    0.3    1.40    1.12
 2      2    0.3    0.1    0.1    0.48    0.34

    To expand this to be more flexible you could use macro variables to specify the list of variable names in the ARRAY statements. You could even generate the list of names to use for the STAGES array by using PROC SQL and INTO clause to extract the names from the STAGE column in the STAGES dataset.

    You can also just follow this example from data_null_ (https://communities.sas.com/t5/SAS-Procedures/Multiplication-of-tables-in-SAS/m-p/125059#M34355) on how to use PROC SCORE to multiply matrices. Setup your STAGES dataset to have the same variable names as your input dataset and include _TYPE_ and _NAME_ variables.

    data stages ;
  _TYPE_='SCORE';
  input _NAME_ :$32. x y z ;
cards;
a 0.7 1.2 1.5
b 0.3 1.1 1.4
;

    Then you can use it to "score" your source data.

    proc score score=stages data=have out=want;
  var x y z ;
run;