04-20-2016 10:31 AM - edited 04-20-2016 10:38 AM
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;
04-21-2016 02:34 AM
04-21-2016 03:30 AM
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 ?
04-21-2016 03:50 AM
04-21-2016 03:55 AM
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:
04-21-2016 05:23 AM
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.
datafile= '....' dbms=xls
replace; *overwrites an existing SAS data set.;
CREATE TABLE xxxx AS
compress(B,,'s') AS A /*not working*/
CREATE TABLE xxxx AS
compress('0802 12',,'s') AS A /*not working*/
,compress('0806 20',,'s') AS B /*not working*/
,compress('2008 11 10',,'s') AS C /*works*/
04-21-2016 02:10 PM
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.
04-21-2016 02:27 PM - edited 04-21-2016 02:28 PM
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;
04-21-2016 05:50 PM
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).
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: