BookmarkSubscribeRSS Feed
Shiring
Calcite | Level 5

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!

16 REPLIES 16
Reeza
Super User

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
Calcite | Level 5
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
Super User

No. Sort your data before the merge. 

Shiring
Calcite | Level 5
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
Super User

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
Calcite | Level 5
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
Super User

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
Calcite | Level 5
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
Super User

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
Super User

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
Super User

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

Shiring
Calcite | Level 5

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


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
  • 16 replies
  • 2016 views
  • 1 like
  • 3 in conversation