Lookup Table with SAS contains

[alexlor]

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.

[Tom · Accepted solution]

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;

[novinosrin]

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

[Reeza]

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

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';

[Reeza]

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

[ballardw]

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

[Tom]

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.

[alexlor]

Whouah Thanks  I didn't know that