Calcite | Level 5

Compute Cumulative Distance between longitude/latitude points

Dear Forum,

I have yet been able to devise a working piece of code that will create a cumulative total column that resets at each break in another variable. Have over 7k in observations.

For example, given the following sample data set.

1   NY   ZIPCODE#      long#   lat#

2   NY   ZIPCODE#      long#   lat#

3   NY    ZIPCODE #    long#  lat#

4   NY    ZIPCODE#     long#  lat#

5   NY   ZIPCODE#      long#   lat#

6   LA    ZIPCODE#      long#   lat#

7   LA    ZIPCODE#      long#   lat#

8   LA    ZIPCODE#      long#   lat#

9   LA    ZIPCODE#      long#   lat#

10  LA   ZIPCODE#      long#   lat#

I will like to compute the cumulative distance (measured in kilometers) between each of the ZIP codes in each state. With the total cumulative distance being shown in the last observation of the given state.

Thank you for any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

Re: Compute Cumulative Distance between longitude/latitude points

``````data want;
set have;
by state;
if first.state then cum_dist=0;
cum_dist+geodist(lat,long,lag(lat),lag(long));
run;``````

Assumes your data is sorted properly by state.

--
Paige Miller
4 REPLIES 4
Diamond | Level 26

Re: Compute Cumulative Distance between longitude/latitude points

``````data want;
set have;
by state;
if first.state then cum_dist=0;
cum_dist+geodist(lat,long,lag(lat),lag(long));
run;``````

Assumes your data is sorted properly by state.

--
Paige Miller
PROC Star

Re: Compute Cumulative Distance between longitude/latitude points

Shouldn't the two statements:

``````    if first.state then cum_dist=0;
cum_dist+geodist(lat,long,lag(lat),lag(long));``````

be reversed?

Otherwise I think the 2nd through last states will start out with the distance between their first zipcode and the last zipcode of the prior state, no?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Diamond | Level 26

Re: Compute Cumulative Distance between longitude/latitude points

@mkeintz wrote:

Shouldn't the two statements:

``````    if first.state then cum_dist=0;
cum_dist+geodist(lat,long,lag(lat),lag(long));``````

be reversed?

Otherwise I think the 2nd through last states will start out with the distance between their first zipcode and the last zipcode of the prior state, no?

Correct! Good catch!

--
Paige Miller
Super User

Re: Compute Cumulative Distance between longitude/latitude points

Or possibly

```data want;
set have;
by state;
if first.state then cum_dist=0;
else cum_dist+zipcitydistance(zipcode,lag(zipcode));
run;```

Depending on where you obtained the lat and long values the ZIPCITYDISTANCE likely varies as it uses the lat, long values from the SASHELP.ZIPCODE data set. However if you have Zipcodes that do not appear in that set you get a missing values which will screw this up significantly.

Discussion stats
• 4 replies
• 616 views
• 1 like
• 4 in conversation