COMPRESS function not working

[metallon]

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

[dsbihill]

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;

[Loko]

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

[metallon]

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;

[Loko]

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 ?

[metallon]

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;

[Loko]

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

[metallon]

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;

[ballardw]

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.

[BrunoSilva]

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

[FreelanceReinh]

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