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

Hello

 

I own 2 tables in my program.

table A

key;value
key1;A and B
key2;A
key3;B

table B
value;value_text
A;text a
B;text b

 

I want receive each line of the table A with the value "value_text" of the table B . There is a problem , tableA.value can contain "A and B" in the even cell ...

 

 

select tableA.key , tableA.value , tableB.value
from tableA
inner join SUBSTR(tableA.value,INDEX(cats(tableA.value),tableB.value)length(tableB.value))) = tableB.value

An idea if the query is logical or an other solution exist ?

 

Thanks for you help.

1 ACCEPTED SOLUTION

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

Yes, I believe I mentioned that the code is not tested.  When posting a question there is a guidance box by the Post button, which shows you what information to post.  This includes, but is not limited to test data - in the form of a datastep in a code window (its the {i} above the post area), required output,Logs.

 

This information is necessary as we cannot see your machine, we don't know what your doing, and hence we have to guess.  

This "which isn't read by SAS" - does not tell me anything?

I guess the error is because there is a typo:

    text_values_sample=substr(findw(text_values,"AND")+3));

Should be:

    text_values_sample=substr(text_value,findw(text_values,"AND")+3));

 You can do it your way, its fine, the basic concept is to break the column which has multiple data items into separate entries, either observations or columns.  You should always keep individual data items in their own column or observation - this is to make processing that data easier.

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its more of a data cleaning issue.  Why do you have a variable with multiple data items in it.  Step one will be to split them into their own variables.  For instance, you could code around it, but you may cause further problems. Take:
B not A

As a string, should A be there?

 

Me I would process table A to look more like:
KEY    VALUE1    VALUE2

1         A                B

2         A

3         B

 

You can then simply merge based on value=value1 or value=value2.  Obviously this doesn't really show it well, but I can only work with what you provide here.

azertyuiop
Quartz | Level 8

Hello ,

 

The table in enter contain 2 column s.

 

In the field "VALUE" if you found "A and B" it's a string text which is contained in one cell of the table.

 

I search to use text function in sas to obtain 2 lines , one line with the value "A" and an other line with the value "B" .

Reeza
Super User

@azertyuiop wrote:

Hello ,

 

The table in enter contain 2 column s.

 

In the field "VALUE" if you found "A and B" it's a string text which is contained in one cell of the table.

 

I search to use text function in sas to obtain 2 lines , one line with the value "A" and an other line with the value "B" .


Instead, consider posting sample data and the corresponding expected output.

 

azertyuiop
Quartz | Level 8

Hello / Good morning ,

 

Here is an concret example :

 

Sans titre.png

TableA and TableB the tables in enter . Te tableC is for the result. When there is the value " and " is the cell I want create 2 lines with each value , value before " and " and after " and " .

 

I want execute this query under SAS :

 

Create table tablec_result as

select distinct
tablea.keys ,
tablea.text_value ,
tableb.text_value ,
tableb.code_value ,
length(tablea.text_value) ,
left(tablea.text_value,length(tableb.text_value)) ,
substr(tablea.text_value,index(tablea.text_value,tableb.text_value)-length(tableb.text_value),length(tableb.text_value))
from work.tablea
inner join work.tableb on index(tablea.text_value,tableb.text_value)= tableb.text_value
and tableb.text_value like substr(tablea.text_value,index(tablea.text_value,tableb.text_value)-length(tableb.text_value),length(tableb.text_value)))
and tablea.text_value like '% and %' ;

The solution is possible in SQl because after a test with a another SGBD (mysql) in an other server , this query is ok. I want translate the query in SAS case.

 

Thanks to correct and give your opinion Man Very Happy

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Step1: Take table A and create the list of data items, not combined - note as you refuse to post test data in a usable format this is just psuedocode:

data inter_tablea;
  set tablea;
  length text_values_sample $50;
  if index(text_values,"AND") > 0 then do;
    text_values_sample=substr(text_values,1,findw(text_values,"AND"));
    output;
    text_values_sample=substr(findw(text_values,"AND")+3));
    output;
  end;
  else do;
    text_values_sample=text_values;
    output;
  end;
run;

That is the only real problem here is that the "data" is all squashed into one variable.  Once you separate out the data items into (in the above case) observations (but you could also do it with two variables) then the merge itself becomes very simple.

Note that you can't really do the index merge as you have elements like:

TEXT BB, which contains TEXT BB and TEXT B, so you need to process these before merging.

azertyuiop
Quartz | Level 8

Hello ,

 

Due to an error in your code I permit to modify this  by this :

 

 

data inter_tablea; 
set inter_tablea;

    do i=1 to count(text_values, "and", " ")+1;
      text_values_sample=scan(tranwrd(text_values," and ",";"),-i,";");
       output;
     end;
run;

Here there is only one step. SAS break each line where there are several values with "and" separator.

 

I have tested your code and there are 2 errors which are detected by SAS :

 

 

text_values_sample=substr(findw(text_values,"AND")+3);
    output;

An error in the " findw " function , which isn't read by SAS .

 

The second error it's SAS which can't create the table "inter_tablea" .

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, I believe I mentioned that the code is not tested.  When posting a question there is a guidance box by the Post button, which shows you what information to post.  This includes, but is not limited to test data - in the form of a datastep in a code window (its the {i} above the post area), required output,Logs.

 

This information is necessary as we cannot see your machine, we don't know what your doing, and hence we have to guess.  

This "which isn't read by SAS" - does not tell me anything?

I guess the error is because there is a typo:

    text_values_sample=substr(findw(text_values,"AND")+3));

Should be:

    text_values_sample=substr(text_value,findw(text_values,"AND")+3));

 You can do it your way, its fine, the basic concept is to break the column which has multiple data items into separate entries, either observations or columns.  You should always keep individual data items in their own column or observation - this is to make processing that data easier.

azertyuiop
Quartz | Level 8
I test you solution later in an other program ans I gave a result.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 2100 views
  • 1 like
  • 3 in conversation