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

Hi All,

 

       I have two sasdatasets , one has column ID which has 8 digit number. The second dataset has a text field in which it has ID from the first dataset any where placed in the text field.  I need a column in the second dataset which says "exists"  if the ID exists in the first dataset.

 

Example :

  First Dataset : 12345678

  Second data set text Value: **Request #12345678-Corporate Membership

 

In the text field the ID value can be anywhere in the text. Basically for every ID in the first dataset it has to loop through all records in the second  dataset and put a flag exists in that row where it matches in the second dataset.

 

Thanks In Advance,

Vani.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

index() function should be able to handle it:

proc sql;
  create table WANT as
  select  A.*,
             case when B.VAL is not null then 1 else 0 end
  from    LONGSTRING A
  left join SMALLDATA B
  on        index(A.VAL,B.VAL)>0;
quit;

This should join the small dataset to the larger one, based on finding the small string in the big string.

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

index() function should be able to handle it:

proc sql;
  create table WANT as
  select  A.*,
             case when B.VAL is not null then 1 else 0 end
  from    LONGSTRING A
  left join SMALLDATA B
  on        index(A.VAL,B.VAL)>0;
quit;

This should join the small dataset to the larger one, based on finding the small string in the big string.

VaniNaga
Calcite | Level 5

Thank you. It did work , got that extra flag column as I want. 

ballardw
Super User

Please provide some example data from each data set and the result. The wording you are describing for output has a couple of different interpretations and an example of what the output looks like would help determine which meaning(s) to use. For instance what if the ID is duplicated in the first set. One interpretation would be to create two or more 'marked' result records in the output.

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
  • 3 replies
  • 639 views
  • 0 likes
  • 3 in conversation