Search code examples
sqlsasproc-sql4gl

SAS proc sql concatenation and join like

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?

Solution

  • 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.