Date:         Fri, 24 Oct 2008 22:21:30 -0400
Reply-To:     Ken Borowiak <EvilPettingZoo97@AOL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ken Borowiak <EvilPettingZoo97@AOL.COM>
Subject:      Re: COMPRESS function modifier not working as expected in PROC SQL

On Fri, 24 Oct 2008 14:17:36 -0400, Dave Scocca <dave@SCOCCA.ORG> wrote:

>--On 10/24/2008 2:12 PM -0400 Jack Clark wrote:
>
>> When I run it in PROC SQL, an ERROR is generated.
>> 754
>>
>> 755  proc sql;
>>
>> 756     select compress(x,,"d") as y
>>
>>                           -
>>
>>                           22
>>
>> ERROR 22-322: Syntax error, expecting one of the following: a name, a
>> quoted string,
>>
>>               a numeric constant, a datetime constant, a missing value,
>> BTRIM, INPUT, LOWER, PUT,
>>
>>               SUBSTRING, UPPER, USER.
>
>It looks like the DATA step allows two consecutive commas to represent a
>missing parameter, while SQL requires an explicit specification of the
>missing parameter between the commas (as . or "").  The following should
>work:
>
>   proc sql;
>      select compress(x,"","d") as y
>      from   test
>      ;
>     quit;
>
>Dave Scocca


Regular expressions offer a lot flexibility in compressing out characters
from a string b/c of the numerous predefined character classes and short-cut
notations available.

Ex:

proc sql _method feedback ;
 create table clnmsg as
 select   text
        , prxchange( 's/\d//', -1, text ) as text_no_digits
        , prxchange( 's/\D//', -1, text ) as text_only_digits
        , prxchange( 's/[[:lower:]]//', -1, text ) as text_no_lower
 from     sashelp.clnmsg
 ;
 quit ;

pax,
Ken