Hash Code

Merging data sets in SAS is extremely important, however sometimes (depending on the size of the data sets you're merging) this can take a long time to run. Once again SuperBass SAS to the rescue. Using the hash code below can speed this process up massively!

data merged_account_information;

      set account1; /*first table you are merging*/

      length gender $6.;

      if _n_=1 then do;

      declare hash h1(dataset:"account_gender"); /*data memorising information from (the other table you are merging with)*/

       h1.defineKey("account_number"); /*define key to merge on*/

       h1.defineData("gender"); /*define variable to bring across*/

       h1.defineDone(); /*closes hash set*/

      end;

      rc = h1.find() ;

      if rc = 0; /*match returns 0*/

      drop rc;

run;

/*to keep files that don’t match type rc ne 0*/

The code above merges the data set account1 with the data set account_gender by account number, and brings across the gender of the owner of each account!

Generally it's a good idea to declare hash on the smaller of the two tables your merging, otherwise you could get system support giving you a call sometime after you've run it! Also don't forget to specify the length of the variable you're bringing across!

Enjoy! P.S....I own a dog!!

Date Evaluation

Filtering the date in your data using the today() function in SAS is great, as it will provide you with all the data with today's date.

However filtering for a certain number of days from today's date (e.g. 50 days) using the today() function (e.g. today()-50) can take a while to run, which is not great if your manager needs the data on their desk by the hour and you want to keep your job!!

Using the %eval function can speed up the process:

%let date= %eval(%sysfunc(today())- 50);

The code above creates the macro 'date' using the %eval function to calculate the value of the system function today()-50.

Now all you need to do is filter the date in your data set greater than &date and you'll have all the data you need in no time!

If you don't believe me try using the time it function to test the difference! http://superbasssas.blogspot.com/2011/07/time-it.html

Creating A Macro Using Data From An Existing Table!

This is a simple piece of code that I use in most of the projects that I create.

PROC SQL noprint;

select distinct age into :agelist separated by ',' from agedataset;

quit;

The code above takes all of the ages from the data set conveniently called agedataset, and places them into a macro (also conveniently) called agelist. This list of ages within the macro is then separated via commas (',').

Now if I want to filter another table for only the ages within the agedataset, all I need to do is filter for ages IN &agelist and SAS will do the rest for me!