compress function not working

[dash]

Hi All,

I am currently trying to match CLOB variable between column of two different datasets.

Both the variable contain dataset like below including tab/extra space in the middle of sentence and special character.

When I used compress function, it is not working.

Can anybody guide me alternative for this.

CLOB variable contain data like below

Gold market's initially tried to    rally during the day on Monday-but then found (quite) a bit of resistance.

Chinese-Foreign Minister  emphasising that both        countries should be sensitive to each other's concern.

How can I remove all spl char and extra space for comparison. 

[sumitpratap]

Use Modifier(KW) with compress function like below:

a=compress(var,,'kw');

Hope this would resolve issue.

[FreelanceReinh]

Hi @dash,

Another approach would be to replace the tabs and "special characters" (assuming these are space characters, e.g. non-breakable spaces 'A0'x) by blanks and then apply the COMPBL function to reduce multiple consecutive blanks to single blanks:

data want;
set have;
clob=compbl(translate(clob,' ','0009A0'x));
run;

(Please adapt the list of characters, here: '00'x, '09'x [tab], 'A0'x, to your needs. You could use the COMPRESS function with 'w' as the third argument in a preliminary step to find out which non-printable characters are present in your data.)

[ballardw]

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

You may need to provide example data from multiple sets when discussing "matching" in different data sets.

[TET_34]

Hi @dash,

The following Oracle function can be useful.

proc sql ;
  connect to oracle ... ;
  create table want as select * from connection to oracle (
select 
regexp_replace(clob_var), '[[:space:]]+', chr(4000)) as new_var,
from mytable a
  );
quit;

Best,