topic Re: Turn Excel matrix formula into SAS formula (convert latitude longitude into RD) in SAS Programming

Turn Excel matrix formula into SAS formula (convert latitude longitude into RD)

Matthijs — Tue, 04 Sep 2018 07:56:14 GMT

Hi,

Case

We have GPS-data (latitude and longitude). We want to find the Dutch zipcode/postalcode of those latitudes and longitudes.

What did we find

We found SAS programming code where we could determine country and province (yeah!). But not the zipcode/postalcode.

So we searched for alternatives. We 'own' a set with dutch zipcodes and de 'Rijksdriehoek-coördinaten (RD)' in X and Y. In theory we can determine de zipcode IF we can convert latitude and longitude to RD-coordinates.

After a day of searching the net we found a matrix-formula in Excel! The next challenge is tranforming the excelformula into SAS.

Excelformula

X = 155000+SUM({190094,945;-11832,228;-144,221;-32,391;-0,705;-2,34;-0,608;-0,008;0,148}*(0,36*(Latitude-52,1551744))^{0;1;2;0;1;3;1;0;2}*(0,36*(Longitude-5,38720621))^{1;1;1;3;0;1;3;2;3})

Y =463000+SUM({309056,544;3638,893;73,077;-157,984;59,788;0,433;-6,439;-0,032;0,092;-0,054}*(0,36*(Latitude-52,1551744))^{1;0;2;1;3;0;2;1;0;1}*(0,36*(Longitude-5,38720621))^{0;2;0;2;0;1;2;1;4;4})

How do we get this formula in SAS?

Example

latitude = 52,07607 --> X = 84514,59

longitude = 4,35903 --> Y = 454697,9

Your help is very welcome.

gr.

Matthijs

Re: Turn Excel matrix formula into SAS formula (convert latitude longitude into RD)

s_lassen — Tue, 04 Sep 2018 08:30:20 GMT

The formula for calculating X can be done like this, by putting the vectors in temporary arrays:

  array constX(9) 8 _temporary_ (190094.945 -11832.228 -144.221 -32.391 -0.705 -2.34 -0.608 -0.008 0.148);
  array expX1(9) 8 _temporary_ (0 1 2 0 1 3 1 0 2);
  array expX2(9) 8 _temporary_ (1 1 1 3 0 1 3 2 3);
  x=155000;
  do _N_=1 to 9;
    x+constX(_N_)*(0.36*(Latitude-52.1551744))**expX1(_N_)*(0.36*(Longitude-5.38720621))**expX2(_N_);
    end;

The stuff for Y can be done the same way.

Re: Turn Excel matrix formula into SAS formula (convert latitude longitude into RD)

Matthijs — Tue, 04 Sep 2018 11:14:56 GMT

Yes! Thank you very much!

Re: Turn Excel matrix formula into SAS formula (convert latitude longitude into RD)

Reeza — Tue, 04 Sep 2018 13:24:41 GMT

If you have the boundary files (spatial files such as SHP) for your postal codes look at GINSIDE to map the points to a spatial area. This will be more accurate than a distance method.