topic Re: isolate several values in the same cell in SAS Enterprise Guide

isolate several values in the same cell

azertyuiop — Fri, 10 Nov 2017 11:18:10 GMT

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.

Re: isolate several values in the same cell

RW9 — Fri, 10 Nov 2017 11:22:06 GMT

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.

Re: isolate several values in the same cell

azertyuiop — Fri, 10 Nov 2017 12:46:25 GMT

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

Re: isolate several values in the same cell

Reeza — Fri, 10 Nov 2017 16:02:51 GMT

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

Re: isolate several values in the same cell

azertyuiop — Mon, 13 Nov 2017 09:20:08 GMT

Hello / Good morning ,

Here is an concret example :

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

Re: isolate several values in the same cell

RW9 — Mon, 13 Nov 2017 09:29:30 GMT

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.

Re: isolate several values in the same cell

azertyuiop — Mon, 13 Nov 2017 10:46:04 GMT

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

Re: isolate several values in the same cell

RW9 — Mon, 13 Nov 2017 11:04:34 GMT

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.

Re: isolate several values in the same cell

azertyuiop — Tue, 14 Nov 2017 10:12:59 GMT

I test you solution later in an other program ans I gave a result.