turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Reflexive join but limit some of the joining

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-09-2016 10:50 AM

I have a constructed a reflexive join but I want to limit some of the process to reduce overhead and get back only the information I need/want.

```
data test;
infile datalines delimiter = ',';
input serial lat long date;
format date date9.;
datalines;
1, 58.6732, 118.2569, 20225
1, 58.6733, 118.2568, 20226
1, 58.9865, 118.3156, 20227
1, 75.259, -52.0023, 20228
1, 75.2689, -52.125, 20229
2, 10.123, 15.256, 20111
2, 10.2365, 14.2698, 20112
2, 11.3569, 15.3489, 20113
3, 23.2569, 0.2659, 20256
3, 22.6789, 0.2788, 20257
3, 12.3569, 35.126, 20258
3, 12.3789, 34.679, 20259
;
run;
data test;
set test;
i = _n_;
run;
%let rad = constant('pi') / 180; /*degrees to radians*/
%let r_km = 6371; /*radius of the earth in km (3959 miles)*/
proc fcmp outlib = work.funcs.haversine;
function haversine(lat1, long1, lat2, long2); /*function name*/
/*function definition*/
*dist = &r_km * 2 * arsin(min(1, sqrt(sin((lat2 - lat1) * &rad / 2)**2 +
cos(lat1 * &rad) * cos(lat2 * &rad) * sin((long2 - long1) * &rad / 2)**2)));
/*asin should be equiv to atan2*/
difflat = lat2 - lat1;
difflong = long2 - long1;
arg = sin(difflat / 2 * &rad)**2 + cos(lat1 * &rad) * cos(lat2 * &rad) * sin(difflong / 2 * &rad)**2;
dist = 2 * &r_km * atan2(sqrt(arg), sqrt(1 - arg));
return(dist); /*what to return*/
endsub;
run;
options cmplib = work.funcs; /*needed to access the function*/
proc sql _method;
create table results (drop = sn date1 j)
as select
a.*,
b.serial as sn label = 'sn',
b.lat as lat1 label = 'lat1',
b.long as long1 label = 'long1',
b.date as date1 label = 'date1',
b.i as j,
haversine(a.lat, a.long, b.lat, b.long) as hdist
from test as a
inner join test as b
on a.serial = b.serial and a.i > b.i;
quit;
```

I added a.i > b.i to remove the symmetric results.

- Is SAS performing the computation and then removing it or is it not executing it all a.i > b.i cases?

How could I trim the cases down to just, for serial 1, dist of 1 with 2, 2, with 3, .... and not run 1 with 3, 1 with 4, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dwsmith

03-09-2016 10:58 AM

I can't tell quite what exact values you are subsetting but the generall approach would be to replace

inner join test as b

with something like

inner join (select your variables go here from test where serial=1 and dist=1) as b

or other conditions on the where. If they are complex enough it may be worth subsetting prior to the join to keep that code cleaner.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dwsmith

03-09-2016 01:46 PM

`on a.serial = b.serial and a.i = a.serial and b.i = a.i+1`

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

03-09-2016 01:53 PM - edited 03-10-2016 10:41 AM

Your solution only returns one item in the table which is not what I was asking.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dwsmith

03-09-2016 02:03 PM

What is "the information I need/want" ? Please post the expected result corresponding to your example data.

PG