topic Re: Multiplication of tables in SAS Programming

Multiplication of tables

Shiring — Wed, 20 Jul 2016 17:55:49 GMT

Hi,

I have two SAS tables:

type Dur1 Dur2 Dur3

1 .1 .2 .4

2 .4 .3 0

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

Re: Multiplication of tables

Reeza — Wed, 20 Jul 2016 18:05:53 GMT

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;

Re: Multiplication of tables

Shiring — Wed, 20 Jul 2016 18:26:28 GMT

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?

Re: Multiplication of tables

Reeza — Wed, 20 Jul 2016 18:44:11 GMT

No. Sort your data before the merge.

Re: Multiplication of tables

Shiring — Wed, 20 Jul 2016 20:54:28 GMT

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!

Re: Multiplication of tables

Reeza — Wed, 20 Jul 2016 21:03:34 GMT

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.

Re: Multiplication of tables

Shiring — Wed, 20 Jul 2016 21:06:46 GMT

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

Re: Multiplication of tables

Reeza — Wed, 20 Jul 2016 21:48:12 GMT

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

Re: Multiplication of tables

Reeza — Wed, 20 Jul 2016 21:49:15 GMT

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;

Re: Multiplication of tables

Shiring — Wed, 20 Jul 2016 22:12:08 GMT

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!

Re: Multiplication of tables

Reeza — Wed, 20 Jul 2016 22:14:31 GMT

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.

Re: Multiplication of tables

Ksharp — Thu, 21 Jul 2016 01:41:17 GMT

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;

Re: Multiplication of tables

Shiring — Wed, 27 Jul 2016 15:04:05 GMT

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!

Re: Multiplication of tables

Ksharp — Thu, 28 Jul 2016 01:36:43 GMT

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;

Re: Multiplication of tables

Ksharp — Thu, 28 Jul 2016 01:43:08 GMT

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

Re: Multiplication of tables

Shiring — Thu, 28 Jul 2016 19:05:58 GMT

Hi, Ksharp, Thank you for your response! However, A & B are columns from data files. when I try to replace A and B using libname.dataname--there are syntax errors. Also, lib.data2 include B but also other variables that's not in the variable --how can I address that?

Moreover, for my table A : it's 4*8 b/c there are only 4 types. for my table B: there are 500k rows while each row has a type in the type column, thus the dimension is 500k*length(duration). Is there still a way to calculate it?

Re: Multiplication of tables

Ksharp — Fri, 29 Jul 2016 05:09:24 GMT

"when I try to replace A and B using libname.dataname--there are syntax errors. Also, lib.data2 include B but also other variables that's not in the variable --how  can I address that?"

Post your code and Log (Error) Information .



"Moreover, for my table A : it's 4*8 b/c there are only 4 types. for my table B: there are 500k rows while each row has a type in the type column, thus the dimension is 500k*length(duration). Is there still a way to calculate it?"

Then IML still can work on that , but need some more code .
Post some data and the output you want to get to explain your requirement if you really want IML code .
Or just try Data Step .