BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alexlor
Fluorite | Level 6
 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
Tom
Super User Tom
Super User

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
Tourmaline | Level 20

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

Reeza
Super User

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
Super User
That's a really nifty solution @Tom - I learn things on here everyday!
ballardw
Super User
eqt
equal to truncated strings (use with character operands only). See Truncated String Comparison Operators.

 

 

Tom
Super User Tom
Super User

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
Fluorite | Level 6

Whouah Thanks  I didn't know that

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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