topic Re: compress function not working in SAS Programming

compress function not working

dash — Tue, 23 Apr 2019 10:16:24 GMT

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.

Re: compress function not working

sumitpratap — Tue, 23 Apr 2019 11:04:30 GMT

Use Modifier(KW) with compress function like below:

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

Hope this would resolve issue.

Re: compress function not working

FreelanceReinh — Tue, 23 Apr 2019 11:53:45 GMT

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

Re: compress function not working

ballardw — Tue, 23 Apr 2019 15:20:54 GMT

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.

Re: compress function not working

TET_34 — Thu, 17 Dec 2020 12:52:30 GMT

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,