As SAS Instructors we travel fairly frequently, and like most of you we have friends scattered throughout the country. High school friends, college friends, family we like (or pretend to). And if I’m on a business trip, I like to see if they are available for drinks or dinner.

As time goes on and people move, and my brain cells deteriorate, I realized I have no idea where people actually live.  I know that my friend Troy used to live in Elida, NM, then moved to Dallas, and then Bethesda, MD, and then to Bellevue, CO. Colorado is a big state though so if I teach in our SAS office in Greenwood Village, CO, I had no idea if he is nearby or a hundred miles away.

So I wanted to write a SAS program to read my Outlook contacts, and then determine the distance between me and my friends.  So two obstacles needed to be overcome:

1. Getting at my contacts info.
2. Figuring out the distance.

So with some sleuthing, I learned how to take my Outlook contacts and write them out to a file, so obstacle 1 was complete.   Obstacle 2 was solved with the SAS zipcitydistance function.

Once those obstacles were overcome, I then created a SAS program and used the tasks within SAS Enterprise Guide that created 2 outputs.

1. A listing report showing me the distance between where I’m going and the 3 friends who are closest to that location.
2. A map of the US pinpointing my Outlook contacts.

So I’m going to share my efforts with you in case you want to do the same. Here are the steps you need.

Export your Outlook Contacts

1. Open your Outlook Contacts window.
2. From the File tab, click on Open then Import (yes I know we are exporting our contacts not importing. Complaints can be sent to Microsoft.)
3. Select “Export to a File”. Click Next.
4. Select “Comma Separated Values (Windows)”. Click Next.
5. Choose your contacts folder. I have categorized mine to have a separate subfolder that is my personal friends (excludes business contacts).  Click Next.
6. Specify the name and location to be c:\temp\mapcontacts.CSV. Obviously, you can put it anywhere you want to, but this is what my SAS code below assumes you named it.
7. Click on Next and replace if prompted.
8. Click on “Map Custom Fields…”
9. Choose the fields of
• First Name
• Last Name
• Home Street
• Home City
• Home Postal Code
10. Click OK. Then click Finish.

Run Your SAS Program

1. Open a SAS session.
2. Bring in the following program.
3. Change the value of the macro variable in the 1^st %LET statement to be the zip code that you are going to.  I know it’s a horrible macro variable name, but I was not in a frame of mind to come up with something better.
4. Run the program and enjoy dinner with your friends!

%let me_go_to=98101;
    data WORK.myzip  (rename=(home_postal_code=zip))             ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile 'C:\temp\mapcontacts.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat First_Name $20. ;
       informat Last_Name $23. ;
       informat Home_Street $28. ;
       informat Home_City $20. ;
       informat Home_Postal_Code 5. ;
       format First_Name $20. ;
       format Last_Name $23. ;
       format Home_Street $28. ;
       format Home_City $20. ;
       format Home_Postal_Code z5. ;
    input
                First_Name $
                Last_Name $
                Home_Street $
                Home_City $
                Home_Postal_Code $
    ;
    if   Home_Postal_Code ne ' ';
        distance=zipcitydistance(Home_Postal_Code, &me_go_to);

  run;

goptions reset=all border;
proc sql outobs=3;
  title '3 friends closest to where I''ll be';
  select first_name, Last_name, distance
  from myzip
  order by distance;
/* Sort the data set by ZIP codes. */
proc sort data=myzip;
  by zip;
run;

/* Create a data set containing the */
/* X and Y values for my ZIP codes. */
data longlat;
 /* In case there are duplicate ZIP codes, rename
    X and Y from the SASHELP.ZIPCODE data set. */
  merge myzip(in=mine)
        sashelp.zipcode(rename=(x=long y=lat)keep=x y zip);
  by zip;
 /* Keep if the ZIP code was in my data set. */
  if mine;
 /* Convert longitude, latitude in degrees to radians */
 /* to match the values in the map data set. */
  x=atan(1)/45*long;
  y=atan(1)/45*lat;
 /* Adjust the hemisphere */
  x=-x;
  /* Keep only the ZIP, X and Y variables */
  keep zip x y;
run;

/* Create an annotate data set to place a symbol at the
   ZIP code locations. */
data anno;
 /* Use the X and Y values from the LONGLAT data set. */
  set longlat;
 /* Set the data value coordinate system. */
 /* Set the function to label. */
 /* Set the size of the symbol to .75. */
 /* Set a FLAG variable to signal annotate observations. */
  retain xsys ysys '2' function 'label' size .75 flag 1 when 'a';
 /* Set the font to the Special font. */
  style='special';
 /* The symbol is a star. */
  text='M';
 /* Specify the color for the symbol. */
  color='red';
 /* Output the observation to place the symbol. */
  output;
run;

/* Combine the map data set with the annotate data set. */
data all;
  /* Subset out the states that you do not want. */
  /* The FIPS code of 2 is Alaska, 15 is Hawaii, */
  /* and 72 is Puerto Rico.  */
  set maps.states(where=(state not in(2 15 72))) anno;
run;

/* Project the combined data set. */
proc gproject data=all out=allp;
  id state;
run;
quit;

/* Separate the projected data set into a map and an annotate data set. */
data map dot;
  set allp;
 /* If the FLAG variable has a value of 1, it is an annotate  */
 /* observation; otherwise, it is a map data set observation. */
  if flag=1 then output dot;
  else output map;
run;

/* Define the pattern for the map. */
pattern1 v=me c=black r=50;

/* Define the title for the map. */
title 'My Friends';
title2 'Based on ZIP Code locations';

/* Generate the map and place the symbols at ZIP code locations. */
proc gmap data=map map=map;
  id state;
  choro state / anno=dot nolegend;
run;
quit;