Multiplication of tables

[Shiring]

Hi,

I have two SAS tables:

A

type Dur1 Dur2 Dur3

1       .1      .2       .4

2       .4       .3       0

B

....type Dur1 Dur2 Dur3 Dur4 Dur5....

.... 1       8       3      3       9        4      .....

...  2       8       4      3       9        4      ......

I want to create a new table C with the following feature:

when type = i in B, use the correponding line in A to multiply and apply the last rate (in this case, Dur3's rate) to all the coloumns pass the matching columns.

i.e.  for the 1st line in B, C would be

....type Dur1 Dur2 Dur3 Dur4 Dur5....

.... 1     8*.1   3*.2  3*.4    9*.4   4*.4      .....    

Also, I would like to change the column names in C--how can I do that in the process of multiplication?

Thank you!

[Reeza]

1. Merge on Type

2. Rename before merge

3. Declare arrays 

4. Find corresponding rate to mulitply

5. Multiply

Untested:

data want;
merge tableA(rename=(dur1-dur3 = rate1-rate3))
           tableB;
by type;

array rate(3) rate1-rate3;
array dur(5) dur1-dur5;
array new_dur(5) new_dur1-new_dur5;

do i=1 to dim(dur);
index=min(i, dim(rate));

new_dur(i)=dur(i)*rate(index);
end;
run;

[Shiring]

I keep getting this error message:
ERROR: BY variables are not properly sorted on data set tableB.
My type is character variable b/c it's actually different names. Is that why?

[Reeza]

No. Sort your data before the merge. 

[Shiring]

Instead of doing a loop, are there faster ways to do this? My dataset is quite large, and I actually have multiple table As (different characteristic functions I need to apply to dataset B). So would it be possible to single out the dur array and compute without sorting, merging, or looping? Thank you so much!

[Reeza]

This is matrix math so possibly IML may be faster. 

Otherwise I'm not aware of one without a loop. You could try to load a temporary multidimensional array to avoid sorting and merging. 

[Shiring]

I'm downloading IML right now. How to temporarily load a multidimensional array? I just transferred into SAS yesterday from R, so super unfamiliar with the syntax

[Reeza]

Downloading IML? It's usually included or not.

Can you explain more, why do you need to run it multiple times? How big are your datasets. What varies each time?

One thing with transitioning from R to SAS - it handles big data well out of the box, you don't have to partition datasets. In fact BY processing is incredibly powerful. 

If you're familiar with R, then IML will be more intuitive to you

[Shiring]

our corporate app database have multiple SAS parts, including SAS IML, but I only downloaded the prereq part.
My dataset has about 100,000 data points, and we are thinking about expand it further.
Right now I am running the multiplication on type, then I need to run it again with gender, length, death tables. so dataset B stays the same, but A changes according to how we partition.

Thank you for your input!

[Reeza]

100,000 is trivial, you'll be fine with the sort/merge method. 

I'd be surprised if it took more than 10 seconds total on a decent set up. 

[Reeza]

Here's an example of temporary arrays:

/*Sample of lookup using Temporary Arrays*/

data scores;
   input Name $ Test_1 Test_2 Test_3;
   datalines;
Bill 187 97 103
Carlos 156 76 74
Monique 99 102 129
;

data M;
do i=10,20,30,40,50,60,70,100,120,130;
	output;
end;
run;



data want;
array M(10) _temporary_;
if _n_=1 then do j=1 to 10;
set M;
M(j)=i;
end;

set scores;

rank=0;
do i=1 to 10 while (rank=0);
if m(i)> test_3 then rank=i-1;
end;

drop i j;
run;

[Ksharp]

Yes. It would be a lot more easy for IML if you make sure Table A and Table B are one to one corresponding .

(e.g.  the first obs of A correspond to the first obs of B, the second obs of A correspond to the second obs of B , .........)

You want IML code ?

data A;
input type Dur1 Dur2 Dur3;
cards;
1       .1      .2       .4
2       .4       .3       0
;
run;
 
data B;
input type Dur1 Dur2 Dur3 Dur4 Dur5;
cards;
1       8       3      3       9        4  
2       8       4      3       9        4 
;
run;  
proc transpose data=a out=temp_a(drop=_:);
by type;
run; 
proc transpose data=b out=temp_b(drop=_:);
by type;
run; 
data temp;
 merge temp_a temp_b(rename=(col1=col2));
 by type;
 want=col1*col2;
run;
proc transpose data=temp out=want(drop=_:) prefix=want;
by type;
var want;
run; 

[Shiring]

IML coding would be very helpful since I just got the package but have no idea how to code. Do I have to have my matrix to be one to one corresponding tho? Is there a way for matrices to auto-search (to match up the type) and directly compute?

Thank you so much for your help!

[Ksharp]

OK. Assuming A and B are row to row corresponding .



data A;
input type Dur1 Dur2 Dur3;
cards;
1       .1      .2       .4
2       .4       .3       0
;
run;
 
data B;
input type Dur1 Dur2 Dur3 Dur4 Dur5;
cards;
1       8       3      3       9        4  
2       8       4      3       9        4 
;
run; 
proc iml;
use A(keep=type);
read all var{type};
close;
use A(keep=Dur:);
read all var _num_ into A;
close;
use B(keep=Dur:);
read all var _num_ into B[c=vnames];
close;

ncol_A=ncol(A);
temp=A[,ncol_A];
do i=1 to ncol(B)-ncol_A;
 A=A||temp;
end;

want=type||A#B;

create want from want[c=('type'||vnames)];
append from want;
close;
quit;

[Ksharp]

" Is there a way for matrices to auto-search (to match up the type) and directly compute?"
No. there is no automatic way to do it. You need write code (UNIQUE-LOC skill) to get it.
But for your example, that would be very easy , since TYPE is 1,2,3,4... which represent the 
index of row of matrix .