Learn everything about Analytics

Joining / Merging in SAS – alternate approaches (including really efficient ones!)

SHARE
, / 3

 

One of the most common operation for any analyst is merging datasets. As per my estimate, an analyst spends at least 10 – 20% of his productive time joining and merging datasets. If you spend so much time doing joins / merges, it is extremely critical that you join datasets in most efficient manner. This is the thought behind this post.

Traditionally, databases have been designed in a manner where tables capture details of individual functional area.

Example below shows two tables, one capturing patient details in a clinic (from one time registrations) and second table showing their appointment details.

Example of data join

Example of data join

In order to analyze things like:

  • Which customer has walked in how many times in last month?
  • Which kind of customers have walked in more last month?
  • What are the common reasons for people walking in?

we need to join the two tables.

 

I’ll cover various ways in which you can do this in SAS:

1. Sort / sort / Merge:

This is the most common approach used in SAS. In order to use data step command, we need to sort the datasets first and then merge using the common key:

proc sort data=patient_details; by pat_id;
proc sort data=appointment_details; by pat_id;

data analysis_set;
 merge patient_details (in=a) appointment_details (in=b);
 by pat_id;
/* note by variables are in the same order as sort by */
if a and b; 

/* Control statement, other options: if a; if b; if not a; if not b;*/
The control statement defines the kind of merge. By specifying “if a and b”, values present in both the tables will be picked.

2. PROC SQL:

If you are used to writing SQL, PROC SQL might be the easiest way to learn joins in SAS

PROC SQL;
      CREATE TABLE analysis_set0 AS
                       SELECT a.*, b.*
                       FROM patient_details a
                       INNER JOIN /* control statement*/
                       /*other options LEFT JOIN, RIGHT JOIN, OUTER JOIN*/
                       appointment_details b
                       ON a.pat_id=b.pat_id;
      QUIT;
 RUN;
3. PROC FORMAT:

This is one of the latest ways I have learnt, but the most efficient one. Using this method, we convert the smaller file into a format.

DATA format1;
           SET patient_details (keep = PAT_ID);
           fmtname = '$pat_format';
           label = '*';
           RENAME pat_id=start;
RUN;
PROC SORT data=format1 nodupkey; 
           by pat_id; 
RUN;
PROC FORMAT CNTLIN=format1;
RUN;

The first step creates a dataset format1 from patient_details. PROC FORMAT then converts it into a format. Finally we use

DATA analysis_set;
     SET appointment_details;
     if PUT(pat_id,$pat_format.) = '*';
RUN;

This way to join datasets typically takes 30 – 40% lower computation time compared to the two approaches mentioned above.

Since this might look advanced SAS, I will devote one more post explaining formats in more details.

In the meanwhile, if you know of any other way to join tables, please let me know.

 

If you like what you just read & want to continue your analytics learning, subscribe to our emails or like our facebook page

3 Comments

  • Zbig says:

    Hi,
    I have a couple of question concerning this:

    1) I understand this is how we create not the ultimate dataset but rather that ANALYSIS_SET is a temporary set to be used for further computations e.g. counting visits in the last month

    2) In the last example age and gender will be missing from the ANALYSIS_SET?

    Another way of merging datasets may be using KEY= option
    not sure if I can paste links here, pls google “Using the KEY= Option for Lookup Tables”

    best regards
    Zbig

  • Pratik Singh says:

    Hi all ,

    I was going through a paper “Choosing the Right Technique to Merge Large Data Sets Efficiently”..the conclusion derived was that,for many to one merge, proc sql is better than data step merge(i am just limiting myself to these two basic merging techniques) on the parameters of CPU Timing,I/O operations and memory.Well, in case of many to many merging Proc SQL is the only one which will bring the desired result.
    So my question here is that,Can we consider Proc SQL to be better than merge for all instances..What about the extra effort used in forming the cartesian product ..is it too little to consider when compared with sorting the datasets in data step merge.

  • Anders Sköllermo says:

    Hi! The solution using a format was very popular many years ago (in version 5). It was mentioned to be much faster that the ordinary solution. There was however one drawback: The formats at that time were load modules, stored in real memory. So there was a limit in practice around 15000 items in the format statement.

    So, this was many years ago. Now a format is a file, with a binary tree. The limits are taken away.

    However, some of the old questions still remain. Is this efficient when the format is only used once? Does this work also on really, really big data volumes?
    This is an intreresting subject, but I do not have the ansers.
    / Br Anders

Leave A Reply

Your email address will not be published.

Join world’s fastest growing Analytics Community
Receive awesome tips, guides, infographics and become expert at: