CODEWITHSUNDEEP
sqlsasproc-sql4gl
rkubic
rkubic

Reputation: 51

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?

Upvotes: 5

Views: 7254

Answers (2)

Joe
Joe

Reputation: 63424

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.

Upvotes: 4

Dejan Peretin
Dejan Peretin

Reputation: 12089

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.

Upvotes: 5

Related Questions