topic Re: Proc SQL to match on part of a text string in SAS Procedures

Proc SQL to match on part of a text string

myrad — Mon, 26 Jan 2009 23:17:00 GMT

Forgive me if this is easy to do but I am new to SAS.

I am trying to do a proc sql and use a text string as the match key. The only problem is that the string changes between 4 or 2 digits and I need to either do a match to the 4 digit if it exists or to match to the 2 digit if the 4 is not available.

For example in table 1 I have:

ABCD
ABDH
EFGH
GHIJ

In table 2 I have:

AB
ABCD
EF
GHIJ

I would want it to match as:

ABCD -> ABCD
ABDH-> AB
EFGH-> EF
GHIJ-> GHIJ

I hope that makes sense?

Re: Proc SQL to match on part of a text string

LinusH — Tue, 27 Jan 2009 09:48:31 GMT

Not really. Your "AB" in table 2 will match both "ABCD" and "ABDH" in table 1, unless you specify some other specification to your matching rule. Nevertheless, your join criteria would look something like this:

where table1.id eq table2.id or substr(table1.id,1,2) eq table2.id

/Linus

Re: Proc SQL to match on part of a text string

deleted_user — Tue, 27 Jan 2009 12:08:38 GMT

have a look at the (sql-only) comparison operators
EQT
LET
GET
NET
etc.

that trailing"T" implies "truncated to shorter"

PeterC Message was edited by: Peter_c