## How to apply function across rows in two datasets

Solved
Frequent Contributor
Posts: 82

# How to apply function across rows in two datasets

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

z1

z2

z3

z4

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?

Accepted Solutions
Solution
‎01-08-2017 04:39 PM
Super User
Posts: 23,992

## Re: How to apply function across rows in two datasets

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

All Replies
Super User
Posts: 5,916

## Re: How to apply function across rows in two datasets

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?
Data never sleeps
Posts: 1,163

## Re: How to apply function across rows in two datasets

[ Edited ]

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;
``````

Thanks,
Jag
Solution
‎01-08-2017 04:39 PM
Super User
Posts: 23,992

## Re: How to apply function across rows in two datasets

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

Super User
Posts: 6,928

## Re: How to apply function across rows in two datasets

[ Edited ]

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;

☑ This topic is solved.