topic Re: How to apply function across rows in two datasets in SAS Enterprise Guide

How to apply function across rows in two datasets

ayin — Thu, 22 Dec 2016 06:22:54 GMT

SAS Enterprise Guide 7.1

I'm trying to write codes to complete the following task:

Here are two datasets

Dataset A Dataset B

Var1 Var2 Var3 Var4

x1.1 x1.2 y1.1 y1.2

x2.1 x2.2 y2.1 y2.2

And the generated dataset/table should be

Dataset C

VarY

whereas z1 = FUNCTION(x1.1,x1.2,y1.1,y1.2)

z2 = FUNCTION(x1.1,x1.2,y2.1,y2.2)

z3 = FUNCTION(x2.1,x2.2,y1.1,y1.2)

z4 = FUNCTION(x2.1,x2.2,y2.1,y2.2)

Var1, Var2, Var3, Var4, VarY are column/variable names.

FUNCTION in this case could be

GEODIST(latitude-1, longitude-1, latitude-2, longitude-2, );

Not sure how to do it, probably Do/Loop is needed?

Re: How to apply function across rows in two datasets

LinusH — Thu, 22 Dec 2016 07:30:18 GMT

Functions are applied to all rows automatically.
What I'm concerned about is that your doesn't seem to have a common key. How to combine your data sets?

Re: How to apply function across rows in two datasets

Jagadishkatam — Thu, 22 Dec 2016 07:54:57 GMT

With regard to combing the datasets then a simple sql code should be good to create a cartesian product. However i am not sure how we could use the function. a catx function could be used to concatenate the var1-var4.

data A;
input Var1$     Var2$;
cards;               
x1.1       x1.2              
x2.1       x2.2  
;

            
data b;
input Var3$     Var4$;
cards;               
y1.1       y1.2              
y2.1       y2.2  
;

proc sql;
create table test as select a.*, b.* from a as a  , b as b;
quit;

Re: How to apply function across rows in two datasets

Reeza — Thu, 22 Dec 2016 07:55:57 GMT

No loop, this is called a CROSS JOIN or cartesian product where all rows are matched with all other rows.

Re: How to apply function across rows in two datasets

Astounding — Thu, 22 Dec 2016 08:45:17 GMT

Here's one approach to assemble all the needed information:

data want;

set a;

set b;

prior_var1 = lag(var1);

prior_var2 = lag(var2);

prior_var3 = lag(var3);

prior_var4 = lag(var4);

if _n_ > 1 then do;

VarY = geodist(prior_var1, prior_var2, prior_var3, prior_var4);

output;

VarY = geodist(prior_var1, prior_var2, var3, var4);

output;

VarY = geodist(var1. var2. prior_var3, prior_var4);

output;

VarY = geodist(var1, var2, var3, var4);

output;

end;

keep VarY;

run;