Multiplication of tables

Reply
Occasional Contributor
Posts: 9

Multiplication of tables

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!

Grand Advisor
Posts: 17,337

Re: Multiplication of tables

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;
Occasional Contributor
Posts: 9

Re: Multiplication of tables

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?
Grand Advisor
Posts: 17,337

Re: Multiplication of tables

No. Sort your data before the merge. 

Occasional Contributor
Posts: 9

Re: Multiplication of tables

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!
Grand Advisor
Posts: 17,337

Re: Multiplication of tables

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. 

Occasional Contributor
Posts: 9

Re: Multiplication of tables

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
Grand Advisor
Posts: 17,337

Re: Multiplication of tables

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

 

Occasional Contributor
Posts: 9

Re: Multiplication of tables

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!
Grand Advisor
Posts: 17,337

Re: Multiplication of tables

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. 

Grand Advisor
Posts: 17,337

Re: Multiplication of tables

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;

 

Grand Advisor
Posts: 9,576

Re: Multiplication of tables

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; 

x.png

Occasional Contributor
Posts: 9

Re: Multiplication of tables

[ Edited ]

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!

Grand Advisor
Posts: 9,576

Re: Multiplication of tables

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;



Grand Advisor
Posts: 9,576

Re: Multiplication of tables

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


Ask a Question
Discussion stats
  • 16 replies
  • 516 views
  • 1 like
  • 3 in conversation