Desktop productivity for business analysts and programmers

How to apply function across rows in two datasets

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

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: 19,167

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. 

 

 

View solution in original post


All Replies
Super User
Posts: 5,388

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
Trusted Advisor
Posts: 1,137

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: 19,167

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: 5,368

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 324 views
  • 3 likes
  • 5 in conversation