topic Re: Lookup Table with SAS contains in SAS Programming

Lookup Table with SAS contains

alexlor — Tue, 29 Aug 2017 16:51:05 GMT

I need to work on SAS and I'm looking for an answer maybe you can help me.

My table looks like:

Table 1:
F:/Drive1/Dataset/Table1
F:/Drive2/Dataset/Table2

My second Table looks like:

 Table 2:
 F:/Drive1
 F:/Drive2
 F:/Drive1/Dataset
 F:/Drive2/Dataset

The Result should looks like:

    F:/Drive1/Dataset/Table1         F:/Drive1
    F:/Drive1/Dataset/Table1         F:/Drive1/Dataset
    F:/Drive2/Dataset/Table2         F:/Drive2
    F:/Drive2/Dataset/Table2         F:/Drive2/Dataset

Using the contains statement it should looks like that.

Any ideas ? I tried a Proc SQL left join, call execute statement.

Thank you for your help.

Re: Lookup Table with SAS contains

Tom — Tue, 29 Aug 2017 17:02:23 GMT

Seems pretty simple.

data have1;
  input folder1 :$60. ;
cards;
F:/Drive1/Dataset/Table1
F:/Drive2/Dataset/Table2
;

data have2;
  input folder2 :$60. ;
cards;
F:/Drive1
F:/Drive2
F:/Drive1/Dataset
F:/Drive2/Dataset
;

proc sql ;
  create table want as 
    select *
    from have1, have2
    where folder1 eqt folder2
   order by 1,2
 ;
quit;

Re: Lookup Table with SAS contains

novinosrin — Tue, 29 Aug 2017 17:25:51 GMT

@Tom Sir, May i request your time to help me understand the eqt operator plz? Many thanks in advance

Re: Lookup Table with SAS contains

Reeza — Tue, 29 Aug 2017 17:35:15 GMT

http://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#n1vzpizmxns3wgn0zsxw2f1jgm0q.htm

Using Truncated String Comparison Operators

Truncated string comparison operators are used to compare two strings. They differ from conventional comparison operators in that, before executing the comparison, PROC SQL truncates the longer string to be the same length as the shorter string. The truncation is performed internally; neither operand is permanently changed. The following table lists the truncated comparison operators:

Truncated String Comparison Operators
Symbol	Definition	Example
EQT	equal to truncated strings	`where Name eqt 'Aust';`
GTT	greater than truncated strings	`where Name gtt 'Bah';`
LTT	less than truncated strings	`where Name ltt 'An';`
GET	greater than or equal to truncated strings	`where Country get 'United A';`
LET	less than or equal to truncated strings	`where Lastname let 'Smith';`
NET	not equal to truncated strings	`where Style net 'TWO';`

Re: Lookup Table with SAS contains

ballardw — Tue, 29 Aug 2017 17:39:08 GMT

eqt	equal to truncated strings (use with character operands only). See Truncated String Comparison Operators.

Re: Lookup Table with SAS contains

Reeza — Tue, 29 Aug 2017 17:42:34 GMT

That's a really nifty solution @Tom - I learn things on here everyday!

Re: Lookup Table with SAS contains

alexlor — Tue, 29 Aug 2017 18:18:47 GMT

Whouah Thanks I didn't know that

Re: Lookup Table with SAS contains

Tom — Tue, 29 Aug 2017 18:36:15 GMT

The T suffix on the operator names in SQL works like the : modifier that you can use in normal SAS programming.

So EQT will compare for equality up to the length of the shorter string. So 'A' eqt 'AB' returns the same results as 'A' =: 'AB' which is TRUE in both cases.