DATA Step, Macro, Functions and more

Lookup Table with SAS contains

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Lookup Table with SAS contains

 

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.


Accepted Solutions
Solution
‎08-29-2017 02:23 PM
Super User
Super User
Posts: 8,287

Re: Lookup Table with SAS contains

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;

 

View solution in original post


All Replies
Solution
‎08-29-2017 02:23 PM
Super User
Super User
Posts: 8,287

Re: Lookup Table with SAS contains

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;

 

Super User
Posts: 2,066

Re: Lookup Table with SAS contains

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

Super User
Posts: 24,025

Re: Lookup Table with SAS contains

Posted in reply to novinosrin

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';
Super User
Posts: 24,025

Re: Lookup Table with SAS contains

That's a really nifty solution @Tom - I learn things on here everyday!
Super User
Posts: 13,942

Re: Lookup Table with SAS contains

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

 

 

Super User
Super User
Posts: 8,287

Re: Lookup Table with SAS contains

[ Edited ]
Posted in reply to novinosrin

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.

Occasional Contributor
Posts: 8

Re: Lookup Table with SAS contains

Whouah Thanks  I didn't know that

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 381 views
  • 9 likes
  • 5 in conversation