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

The following email landed in my inbox this morning and it is such a good question that I decided to share because it comes up often. (If you are the sender, thank you for sending this message and pulling me out of a no-blogging-recently slump!)

Dear Cat,
I need some help and I really don’t have a clue where to start. I was introduced to cubes in a SAS programming class, which are really useful for slicing and dicing my data to understand how my key metrics change with critical variables. But my question is this: How do I go about making a determination of which critical variables I should use? Specifically, for the cube I want to to determine which variables to include in a hierarchy and the order to place them. In my work, there is a lot of data available, indeed too much. How can I sort through the different types of data to focus on the variables that really make a difference, and then use these to build the cube?
-Confused**3

Dear Confused**3,

What you have explained, better than I ever could, is the justification for using statistical models. While it's true that cubes are useful for exploring multidimensional relationships between business variables and your target metrics, once you get past 2 or 3 dimensions, cubes become unwieldy. Then you need something to prioritize the business variables, and even then, sometimes there are too many variables, and most variable reduction techniques only look at one pair of variables at a time, which means you completely miss out on things like, demand for seasonal items depends on the time of year, and other interaction effects. Unless you know a priori to include those variables in the cube, you miss the critical relationships.

Another problem with cubes lies in binning your continuous variables. This is a good practice for finding nonlinear associations in the data, but you miss out on the simplicity of interpretations such as, "What is the expected increase in revenue for a $10 increase in advertising expenditure?" You also have to pick (usually) arbirtary split points for grouping, instead of optimizing the split point based on how the data relate to the target metric. For example, splitting temperature into 3 bins where temperature ranges from (-10 to 90 F), you will see mean ice cream sales for under 23 degrees, 24-57 degrees, and 57 degrees and up. But common sense and many decades of eating ice cream tell me that the critical split point is around 80 degrees. Any colder and the kids are buying other kinds of treats.

So what do you do?

I'm glad you asked.

Regression models, neural networks, decision trees, discriminant function models, and many other statistical modeling techniques are suited for these kinds of problems. Some models, such as regression, have built-in solutions to the too-many-variables problem via stepwise or all-subsets selection. Other models, such as decision trees, deal with the problem by ignoring irrelevant variables. Trees also optimize the split points of continuous variables as in the temperature and ice cream demand problem above. All of them are complemented by dimension reduction techniques such as variable clustering. Some of these models are fitted through procedures in SAS/STAT, while others require more specialized software such as SAS Enterprise Miner or JMP. If there is a time dimension in the data, then you really want to pull out the statistical big-guns and consider the kinds of models that are used to forecast time series.

The primary advantages of using statistical modeling techniques over cubes are that they can work with a large number of inputs, find complex higher-order relationships (two-way interactions are just the beginning), and best of all, leave you with a list of the business variables that make the most difference. And, well, really this is the best of all: a predictive model can be used to score new data without refitting the model or having to recreate the cube. Diagnostics make it possible to determine whether your population has shifted and the model needs to be re-estimated. Most of these models are easy to construct and easier to score. Results can be presented to a non-technical audience with simple graphics.

One caution about using simple methods to describe complex systems: ignoring important dimensions can result in an incorrect interpretation of the data. Take, for example, Simpson's Paradox, which occurs when the within-group relationship between variables is functionally different from the between-group relationship. For example, within a country, auto fatalities are negatively associated with income. However, across countries, greater wealth is associated with more auto fatalities. The classic error is to draw some kind of (causal) inference based on either of these pieces of information. Analyzing all the relevant variables together can lead the analyst to a more meaningful conclusion.

So the next time you're sitting down to an afternoon of cube-making, ask yourself: do I really want to go to that much trouble, when a statistical model will be faster, easier, have a longer shelf-life, and give a more realistic picture of the truth? And then spend the rest of your afternoon eating ice cream.

If you would like to learn more about these kinds of techniques, check out some of these classes. And if you have a cool story about replacing simple descriptive reports with statistical models, leave a comment.

We started out in Episode 1 cleaning and standardizing data from the Academy.  As you recall, Mr. Vader apparently had a attitude when making his entry in the database.  The original data looked like this:

Data set MixedUp

You can find the code for creating the original data set back in Episode 1. There, we demonstrated the power of the COMPRESS function which, when combined with a nice little picture format, did the initial data cleanup:

Data set: CleanedUp1

In this episode, we'll finish cleaning up the last problem: Mr Vader insisted on entering his height and weigh data in Imperial vs. metric units (our standard).  Once again, we'll leverage the power of the COMPRESS function, but two additional functions will help make our code succinct and efficient: FIND and IFN.

The FIND function searches  for a specific substring of characters within a character string, and can do case-sensitive searches or ignore case.  It returns a 0 (boolean false) if the text is not found, or a number indicating the position in the larger string where the text of the smaller substring starts (boolean true).

The IFN function returns a numeric value based on whether an expression is true, false, or missing. The first argument is the expression to evaluate for true or false, the second is the expression producing the value to return if true, the third the expression producing the value to return if false.

To fix the height data, we will COMPRESS out everything except the digits, just like we did for the phone number.  But before storing the value in the Height variable, we'll test to see if it was entered in inches by trying to FIND 'in' in the original text value.  If found, we'll multiply the value by 2.54 (there are 2.54 cm/in) before storing it in the Height variable.  We'll use a similar process for weight. If the weight was given in pounds, we'll divide the value by 2.2046 before storing it in the Weight variable.

Here is the syntax to determine if the text variable ht contains the text 'in' using the FIND function:

   find(ht,'in','i')

Now, when we process Mr. Vader's height information, it DOES contain the string 'in' so our code must use conditional processing to decide to do the math. We'll use the IFN function for this.

Here is the syntax which conditionally determines a multiplier value based on the results of the FIND function:

      IFN(find(ht,'in','i'),2.54,1)

Using similar syntax to determine the divisor for weight, and adding new picture formats for our height and weight values, we get this final "cleanup code":

proc format;
   picture phone (default=13) low-high='000)000-0000' (prefix='(');
   picture kg (default=7) low-high='0000 kg' ;
   picture cm (default=7) low-high='0000 cm' ;
run;
 
data CleanedUp;
   LENGTH Name $ 20 Phone Height Weight 8;
   LABEL Name='Student Name'
         Phone='Primary Phone Number'
         Height='Height'
         Weight='Weight';
   keep Name--Weight;
   format Phone phone. height weight 5.;
   set MixedUp;
   /* Compress to keep only alpha characters */
   Name=Compress(Name,'. ','ka');
   /* Compress to keep only digits. */
   Phone=input(compress(ph,,'kd'),15.);
   /* Compress to keep digits.  If originally in inches, convert value */
   Height=input(compress(ht,,'kd'),15.)*IFN(find(ht,'in','i'),2.54,1);
   /* Compress to keep digits.  If originally in lbs, convert value */
   Weight=input(compress(wt,,'kd'),15.)/ifn(find(Wt,'lbs','i'),2.20462262,1);
   format Weight kg. Height cm.;
run;

And that did the trick! Our final, cleaned up data looks great:

Data set CleanedUP

You may be wondering about my recent fascination with data cleaning... did I mention that I also recently started teaching Ron Cody's "Data Cleaning Techniques" class? I've been preparing to teach it for the first time in March - and thought I'd share a few of the things I'd learned with you.

That's all for this episode. Until the next time, may the SAS be with you!
Mark

How we love shortcuts. Our life seems more efficient with them. I try to take the earlier train for a good parking spot that lets me exit early. Actually change that to any spot (my train station has tons of commuters with FEW parking spaces so getting up early is the only choice). But some of these shortcuts come at a cost. Remind me to tell you all about this Natural Law that SAS also respects in a future post.

Don’t we all try to recreate our world with a bare minimum of manual work? Our perfect world would have machines do all our bidding and our repetitive manual work leaving us completely free to dream up better and faster technology and solutions. While you revel and enjoy this dream for a few seconds, allow me to bring you gently back to reality with three practical tips to help you do just that. I use these, time and over again to help manage my SAS display manager session.

Shortcut #1 - Check the log first

You’re probably familiar with the theme song you’ve heard in a SAS training class “Always check the log first.” Well, isn’t it possible to let SAS make the log its window of first choice instead of hitting all those keystrokes to get to the log?  Absolutely! Here’s what you do:

1. Make the log window your active window.
2. Type this code into your command window and hit enter.

Autopop on; wsave;

Now run a SAS program, any SAS program. See how it takes you directly to the log without you having to activate the log window?

Shortcut #2 - Clear log, output & submit code in one keystroke

Fast on the heels of my last tip is the question that’s probably on the tip of your tongue: But the log is a chronological log, sometimes I want to see my entire log history. Other times I just want to see the most recent log and output. How?  With these steps:

1. Activate your function keys by typing Keys in your command window.
2. Find a function that hasn't been programmed yet.  I'll pick F12.
3. Type in this command:

Clear Log; Clear Output; Submit;

Now run any program by hitting the F12 key or whichever key you used as your shortcut for this tip. See how it automatically clears your log, output windows and submits your SAS program all in one masterful keystroke! Needless to say you don’t have to use this programmed key when you want to see the log and output accumulated.

Shortcut #3 - Automatically assign libraries at startup

Library assignments can be a drag sometimes. All you want is for your libraries to be automatically assigned in your session. Can you let SAS do this? Sure, here’s how (FYI, I’m suggesting an interactive method here but if you prefer writing code, you can certainly embed multiple Libname statements in your programs to assign them at startup or even include them in your autoexec.sas program file).

1. Click on the New Library icon.
2. Enter the library name and path.
3. Check Enable at startup.

Next time you open your SAS session, you’ll see the ORIN library is automatically assigned without any manual interference.

Hope you found these tips useful. I always share them with students who take my SAS programming classes.  I’m sure you have many more that you use. I’d love to hear your tips and also what you’d like to read about in my blog posts. Do you like them short or long? Looking forward to your ideas.

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;