BookmarkSubscribeRSS Feed
metallon
Pyrite | Level 9

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

10 REPLIES 10
dsbihill
Obsidian | Level 7

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
Barite | Level 11

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

metallon
Pyrite | Level 9
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
Barite | Level 11

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
Pyrite | Level 9
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
Barite | Level 11

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
Pyrite | Level 9

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
Super User

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
Quartz | Level 8

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
Jade | Level 19

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 4848 views
  • 3 likes
  • 6 in conversation