SAS proc sql concatenation and join like

Question

I'm trying to build a data model in SAS structures for Financial System Data. The idea is to keep IDs in a table as character expression and then build a proc sql with INNER JOIN to them from another table - the LIKE should get IDs which start with a 'ID%' expression. The proc sql should look like this:

proc sql;
SELECT I.* 
  FORM tableWithIDs X INNER JOIN tableWithData I 
    ON I.ID LIKE X.ID||'%'
;
quit;

Unfortunately, it should look like this: ... LIKE 'X.ID%' Is there a way to build such expression in SAS code?

Answer 1

If you save the value of X.ID||'%', you will probably find that it looks something like 1 % (for id=1). Try putting compress around X.ID||'%' to remove that extra blanks, like so:

LIKE compress(X.ID || '%')

I assume you don't have any blank characters within your IDs.

Answer 2

The SAS solution for this, assuming you're in version 9, is to use the CATS function instead of || concatenation:

like cats(x.ID,'%')

You also have the EQT operator, which means essentially 'starts with' (truncates to equal length, then compares):

on I.ID eqt x.ID

That only works if x.ID is consistently the same length or longer than I.ID, because it will truncate whichever is longer. If I.ID is the longer string, say 'Member', and x.ID is 'Mem', the comparison will be valid, where LIKE would not consider it valid.