topic COMPRESS function not working in SAS Programming

COMPRESS function not working

metallon — Wed, 20 Apr 2016 14:25:10 GMT

Hello,

any ideas why compress is not getting rid of the space in the middle?

proc sql;
CREATE TABLE xxx AS
SELECT
A AS CommDesc
,COMPRESS('0802 12','') AS CN
from yyy;
run;

Thanks

Re: COMPRESS function not working

dsbihill — Wed, 20 Apr 2016 14:38:14 GMT

need to tell it to remove spaces, Try this. Also you should use quit, not run to end a proc SQL.

proc sql;
CREATE TABLE xxx AS
	SELECT
 		A AS CommDesc
		,COMPRESS('0802 12',' ') AS CN
		;
Quit;

Re: COMPRESS function not working

Loko — Wed, 20 Apr 2016 14:36:19 GMT

proc sql;
CREATE TABLE xxx AS
SELECT
A AS CommDesc
,COMPRESS('0802 12',,'s') AS CN
from yyy;
run;

Re: COMPRESS function not working

metallon — Thu, 21 Apr 2016 06:34:22 GMT

Hi dsbihill,
Thanks for the hint. But that did not do the trick.
@Loko, thanks for the example. That worked in this particular case but in no other 🙂

proc sql;
CREATE TABLE xxxx AS
SELECT
,COMPRESS('0709 30 00',,'s') AS CNA /*does not work*/
,COMPRESS('ex 1211 90 86',,'s') AS CNB /*does not work*/
,COMPRESS('0802 11',,'s') AS CNC /*does not work*/
,COMPRESS('0802 12',,'s') AS CND /*works*/
from yyyy;
quit;

Re: COMPRESS function not working

Loko — Thu, 21 Apr 2016 07:30:38 GMT

hello,

I have run the code you have provided and it works even for the examples you said "does not work" .

Are you sure it is a space there when you run the code in sas and not another character ?

Re: COMPRESS function not working

metallon — Thu, 21 Apr 2016 07:50:36 GMT

odd. I go to a colleague now and check it at his PC.
its a space, see for yourself:

proc sql;
CREATE TABLE xxxx AS
SELECT
COMPRESS('0709 30 00',,'s') AS CNA /*not working*/
,COMPRESS('ex 1211 90 86',,'s') AS CNB /*not working*/
,COMPRESS('0802 11',,'s') AS CNC /*not working*/
,COMPRESS('0802 12',,'s') AS CND /*works*/
,compress('ex 1211 90 86',,'s') AS CNE
from sashelp.class;
quit;

Re: COMPRESS function not working

Loko — Thu, 21 Apr 2016 07:55:08 GMT

yes, strange. Maybe experts on the forum have an explanation for it.

anyway within the sas compress help function it is clearly stated the behaviour of S and there is also an example for it:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212246.htm

Re: COMPRESS function not working

metallon — Thu, 21 Apr 2016 09:23:57 GMT

Still not working.
Its a character variable, length 21. I importet the data from a .xls
the attached screen print shows the data.
if I copy and paste it I can detect the spaces/blanks.
and If I go over the data set with a proc sql and compress its not working.

proc import
     datafile= '....' dbms=xls
     out=yyyyy
     replace; *overwrites an existing SAS data set.;
     getnames=no;
     datarow=2;
run;

proc sql;
CREATE TABLE xxxx AS
SELECT
compress(B,,'s') AS A /*not working*/
from yyyyy;
quit;

/*further test*/
proc sql;
CREATE TABLE xxxx AS
SELECT
compress('0802 12',,'s') AS A /*not working*/
,compress('0806 20',,'s') AS B /*not working*/
,compress('2008 11 10',,'s') AS C /*works*/
from sashelp.class;
quit;

Re: COMPRESS function not working

ballardw — Thu, 21 Apr 2016 18:10:42 GMT

Have you tried

compress('0802 12',' ','s') AS A

where the second arguement is a single space?

The S operator ADDS characters to the string of the second argument. But I suspect if there is not a string there nothing is added.

Re: COMPRESS function not working

BrunoSilva — Thu, 21 Apr 2016 18:28:25 GMT

Hello,

I suspect that what you have is non-printable characters and not space.

If the values are only Numeric try this:

proc sql;
CREATE TABLE xxx AS
SELECT 
 A AS CommDesc
,COMPRESS('0802 12','N') AS CN 
from yyy;
quit;

Best Regards

Re: COMPRESS function not working

FreelanceReinh — Thu, 21 Apr 2016 21:50:55 GMT

Hello @metallon,

To get a definitive answer as to what kind of "blank" character you have in the strings in question, you should print (a few of) them with $HEXw. format (with w >= twice the length of the string).

Example:

data _null_;
s1='123 456';
c1=compress(s1,,'s');
s2='123 456'; /* The "blank" here is a protected blank, 'A0'x! */
c2=compress(s2,,'s');
put (s: c:) (=);
put (s: c:) (= $hex14.);
run;

Result (colors added):

s1=123 456 s2=123 456 c1=123456 c2=123 456
s1=31323320343536 s2=313233A0343536 c1=31323334353620 c2=313233A0343536

(Surprisingly, my SAS 9.4 (TS1M2) does not compress the protected blank, contrary to what is stated in the documentation.)

Then you can use the appropriate arguments of the COMPRESS function to get rid of these blank characters.

Alternatively, @BrunoSilva's suggestion to use a positive list of characters to keep (such as "only digits") can be helpful in this situation. You have to include the k ("keep") modifier, though:

compress('0802 12',,'kn')