Desktop productivity for business analysts and programmers

isolate several values in the same cell

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

isolate several values in the same cell

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.


Accepted Solutions
Solution
a month ago
Super User
Super User
Posts: 8,152

Re: isolate several values in the same cell

Posted in reply to azertyuiop

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


All Replies
Super User
Super User
Posts: 8,152

Re: isolate several values in the same cell

Posted in reply to azertyuiop

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.

Contributor
Posts: 56

Re: isolate several values in the same cell

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" .

Super User
Posts: 20,203

Re: isolate several values in the same cell

Posted in reply to azertyuiop

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.

 

Contributor
Posts: 56

Re: isolate several values in the same cell

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

Super User
Super User
Posts: 8,152

Re: isolate several values in the same cell

Posted in reply to azertyuiop

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.

Contributor
Posts: 56

Re: isolate several values in the same cell

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" .

Solution
a month ago
Super User
Super User
Posts: 8,152

Re: isolate several values in the same cell

Posted in reply to azertyuiop

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.

Contributor
Posts: 56

Re: isolate several values in the same cell

I test you solution later in an other program ans I gave a result.
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 176 views
  • 1 like
  • 3 in conversation