SAS programming tips & tricks, certification advice, and classroom reports from SAS trainers

In my previous blog post I talked about how to map your Outlook contacts and create a list of the 3 people nearest the zip code you were traveling to. That’s all fine and dandy, but my friends often don’t update me when they move and we just connect via Facebook.  Wouldn’t it be nice to know where your Facebook friends are?

Much like the earlier blog post, I 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 Facebook friends.

My Facebook and real life friend Roger wrote a blog on how to do this with R and Python Scripts. I figured I could do this with SAS.  Roger encountered several obstacles and he helped me greatly with some of the access token stuff. I encountered my own obstacles like one of my friends has an apostrophe in his first name. And you SAS programmers know how much SAS loves to wreak havoc with unbalanced quotes!

So the code is below and first a few disclaimers:

1. This is not production level code. You may look at it and say “oh, you should have done it this way”, and “that part isn’t very efficient,” and you are absolutely right. Sometimes it’s important just to get things done, not necessarily to get things done perfectly.
2. Facebook gives you what it gives you. I have at least one instance where I can clearly see a friend’s location in his profile, but it is not being returned back by the access token. This seems to be a rare occurrence, but be aware that it happens.
3. SAS gives you what it gives you. The SASHELP.ZIPCODE data set doesn’t list all cities everywhere. So friends that live in small towns might not get picked up. And of course my friend who lives in London, while it’s a fairly large city, doesn’t get picked up either.

Big thanks goes out to Roger, Cat Truxillo, and Andy Ravenna for help and testing!

So here is how to map your US Facebook friends using SAS, as well as find the 3 friends that are nearest a particular zip code.  Here are the steps you need.

Get the appropriate Facebook info

1. Go to this Facebook developers page
2. Click on "Get Access Token" and choose the fields

• User_hometown User_location from the User Data Permissions tab

• from the Friends Data Permissions tab, choose friends_hometown and friends_location

NOTE: Not all of these are used in the program but the code is set up to assume this and only this is what is coming in.

1. Click Get Access Token, then click Log In with Facebook.
2. If necessary, click Submit.  (Sometimes the website will freeze on you).
3. Copy the access token returned. NOTE: I think this expires after 24 hours.
4. Go to https://graph.facebook.com/me/friends?access_token=????? (paste in the access token where the question marks are. )
5. You will get prompted if you want to open or save the file. Click on the down arrow next to Save and choose Save As.  Save it to C:\temp and name it facebookfriendsid.txt   NOTE: you may have to use Windows Explorer to rename it.

The only reason we are doing steps 6 and 7 is because the token expires, it's nice to have the file of your Facebook friends on your C drive. You can skip steps 6 and 7 by using the URL directly in your FILENAME statement, but that would need to be changed every day.

1. Include the below SAS program and in the 1st %LET statement plug in your zip code or one you are traveling to.
2. About 1/3 of the way down the program, change the token to be the same that you copy and pasted from earlier.  I really haven’t found a way around this.
3. Submit the program and enjoy dinner with your friends!

SAS Program

%let me_go_to=98101;

data fball (keep=name id);
infile 'c:\temp\facebookfriendsid.txt' dlm=':' firstobs=3;
input  crap $ name_field $ name :$40.
       id_field $ id :$17./
;
name=substr(name, 2,length(name)-3);
id=substr(id,2);
id=compress(id,'"');
run;

proc sql noprint;
select nobs into :numfriends
from dictionary.tables where LIBNAME='WORK' and memname='FBALL';
%let numfriends=&numfriends;
%put numfriens is &numfriends;

select name, id into :name1-:name&numfriends, :id1-:id&numfriends
from fball;

options nomprint nosymbolgen;
%*let numfriends=5; /*using for testing */
%macro loop;
%do i=1 %to &numfriends;
%put working on %bquote(&&name&i) whose id is %bquote(&&id&i);

filename fbfriend url "https://graph.facebook.com/&&id&i?access_token=AAACEdEose0cBAPAvLcJMjMAJPD6c45X25sZA2lCdQPJDypZAY5J6ZCTy8yOG8ysD1FbrjSouTZBgg3iEdmPmDDCilez9t90edSw6DeeeQ1HBhY5PsWBI";

data fbfriend&i ;
keep name location;
length location $50;

infile fbfriend length=len lrecl=500;
input record $varying1000. len @;

   put record $varying1000. len;
   namestart=index(record, 'name');
   namepart=substr(record,namestart+7);
   endlocation=index(namepart,'"');
   name=substr(namepart,1,endlocation-1);

   locationstart=index(record,'location');
   if locationstart>0; /*otherwise friend does not publish location */
partial=substr(record,locationstart);
locationnext=index(partial,'name');
startlocation=substr(partial,locationnext+ 7);
endlocation=index(startlocation,'"');
location=substr(startlocation,1,endlocation-1);
run;

filename fbfriend clear;
%end;

data combine;
length city $ 35 state $25; 
drop location;
set
  %do j=1 %to &numfriends;
  fbfriend&j
  %end;
; /*end set statement */
city=scan(location,1, ',');
state=scan (location, 2, ',');
state=substr(state,2);
run;
%mend;
%loop

proc sql;
create table merged as
select name, a.city, a.state, zip
from combine a, sashelp.zipcode z
where trim(upcase(a.city))=trim(upcase(z.city))
    and trim(upcase(a.state))=trim(upcase(z.statename))
order by name;

data merged2;
set merged;
by name;
if first.name;
run;

proc sort data=merged2 out=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;

goptions reset=all border;

/* 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 Facebook Friends';
title2 'Based on City 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;

/* create listing report of 3 closest friends */
proc sql outobs=3;
  title '3 friends closest to where I''ll be';
  select name, city, state,
      zipcitydistance(zip, &me_go_to) as distance 

  from myzip
  order by distance;

/* find friends that have a location but are not getting mapped */
  proc sort data=combine out=full;
by name;

proc sort data=merged2 (drop=zip) out=most;
by name;

data missing;
merge full (in=all) most(in=most);
by name;
if all and most then delete;
run;
proc print;
title 'not in list';
run;

Since we're all pretty excited about it around here, you may have already heard that today SAS ranked #3 in the 2012 FORTUNE 100 Best Companies To Work For list.  This is the 15th consecutive year that SAS has prominently ranked on the list - since its beginning!

I thought I would take a quick moment to tell you what it means to me to work at SAS and why I love my job. Professionally, SAS has allowed me to grow by continuing to provide challenging and creative work.  Every day I learn something new.  And I'm continually blown away by the people I work with.  They are some of the most intelligent, innovative, funny and kind people I've ever met.  I feel lucky to be able to say that I get to work with my friends.  Personally, SAS has become my extended family.  My SAS family has seen me through my marriage, the birth of my children (who attend the on-site daycare here at SAS), and provided an incredible amount of support and comfort during a time of personal tragedy. Of course I love the gym, healthcare center, cafes and all the other perks, but what really makes SAS the best place to work is the people.

Okay, that's enough of that mushy stuff.  If you're interested in learning more about the history of SAS check out this corporate timeline video.