Using COMPRESS function inside a PROC SQL with modifiers isn't allowed

[deleted_user]

Hi,

I thought of using the compress function with modifier inside the PROC SQL, but it isn't allowed, The expression "Compress(CRED_CUST_No, ,'c')" been used in the ETL mapping itself, so DI generates the proc SQL, and PROC SQL isn't allowing the modifier as part of the COMPRESS function.

you may refer the log below...

2857 proc sql;
2858 create table work.W5TR1SOO as
2859 select BS_MANDATE_NO length = 6,
2860 CRED_CG_ID length = 6,
2861 (COMPRESS(CRED_CUST_NO, ,'c')) as CRED_CUST_NO length = 32 informat = $ebcdic32.,
_
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.

Workaround for the above is using Compress function with the DATA STEP processing by customizing the pre generated source code.

Any other work around to eliminate the control characters using PROC SQL? so I can stop customizing the SAS transformation.

Thanks and Regards,
AnjiREDDY.

[Patrick]

This code run with PC SAS 9.22 works fine for me.

data have;
var=cats('1','0A'x,'0D'x,'2');
run;

proc sql ;
select
var,
compress(var,,'c') as cvar length = 32 informat = $ebcdic32.
from have
;
quit;

[deleted_user]

Hi,

You may be right, in the SAS version 9.2 you might be able to use compress function with modifiers in proc sql, unfortunately we are still using 9.1.3.4, our SAS is yet to be upgraded to 9.2. PROC SQL isn't allowing us to use compress fucntion with modifier as I posted above, but we are able to use the same in data step processing.

Could you please help me to write the sql query to ignore those control characters?

Regards,
AnjiReddy.

[sbb]

You can code the 2nd argument as an explicit list of characters to compress out, and omit the third argument altogether. Use the concatenation operator and create a string to list your characters.

Scott Barry
SBBWorks, Inc.

SAS 9 Language Dictionary, Function and CALL Routines
COMPRESS Function
Removes specific characters from a character string
http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000212246.htm

[deleted_user]

Hi,

As you advised, I have been trying to concatenate the control characters into a string but it throws an error when I tried to invoke the macro function below

%macro concat_ctrl(contstr=,i=);
%do %while(&i < 32);
%let contstr = %sysfunc(cats(&contstr,%sysfunc(byte(&i))));
%put &contstr &i;
%let i=%eval(&i+1);
%end;
%mend concat_ctrl;
%concat_ctrl(contstr=%sysfunc(byte(0)), i=1);

You may refer the log below
MLOGIC(CONCAT_CTRL): Beginning execution.
MLOGIC(CONCAT_CTRL): Parameter CONTSTR has value
MLOGIC(CONCAT_CTRL): Parameter I has value 1
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 32) loop beginning; condition is TRUE.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to
SYMBOLGEN: Macro variable I resolves to 1
ERROR: %SYSEVALF function has no expression to evaluate.
ERROR: %SYSEVALF function has no expression to evaluate.
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to
SYMBOLGEN: Macro variable I resolves to 1
1
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable I resolves to 2
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 32) condition is TRUE; loop will iterate again.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to
SYMBOLGEN: Macro variable I resolves to 2
ERROR: %SYSEVALF function has no expression to evaluate.
ERROR: %SYSEVALF function has no expression to evaluate.
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to
SYMBOLGEN: Macro variable I resolves to 2
2
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable I resolves to 3
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 32) condition is TRUE; loop will iterate again.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to
SYMBOLGEN: Macro variable I resolves to 3
ERROR: Expected close parenthesis after macro function invocation not found.
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to
SYMBOLGEN: Macro variable I resolves to 3
3
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 3
SYMBOLGEN: Macro variable I resolves to 4
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 32) condition is TRUE; loop will iterate again.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to
SYMBOLGEN: Macro variable I resolves to 4
ERROR: Expected close parenthesis after macro function invocation not found.
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to
SYMBOLGEN: Macro variable I resolves to 4

But I am able to invoke the macro function above with difference values

%macro concat_ctrl(contstr=,i=);
%do %while(&i < 72);
%let contstr = %sysfunc(cats(&contstr,%sysfunc(byte(&i))));
%put &contstr &i;
%let i=%eval(&i+1);
%end;
%mend concat_ctrl;
%concat_ctrl(contstr=%sysfunc(byte(65)), i=66);

You may refer the log below
MLOGIC(CONCAT_CTRL): Beginning execution.
MLOGIC(CONCAT_CTRL): Parameter CONTSTR has value A
MLOGIC(CONCAT_CTRL): Parameter I has value 66
SYMBOLGEN: Macro variable I resolves to 66
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 72) loop beginning; condition is TRUE.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to A
SYMBOLGEN: Macro variable I resolves to 66
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to AB
SYMBOLGEN: Macro variable I resolves to 66
AB 66
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 66
SYMBOLGEN: Macro variable I resolves to 67
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 72) condition is TRUE; loop will iterate again.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to AB
SYMBOLGEN: Macro variable I resolves to 67
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to ABC
SYMBOLGEN: Macro variable I resolves to 67
ABC 67
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 67
SYMBOLGEN: Macro variable I resolves to 68
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 72) condition is TRUE; loop will iterate again.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to ABC
SYMBOLGEN: Macro variable I resolves to 68
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to ABCD
SYMBOLGEN: Macro variable I resolves to 68
ABCD 68
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 68
SYMBOLGEN: Macro variable I resolves to 69
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 72) condition is TRUE; loop will iterate again.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to ABCD
SYMBOLGEN: Macro variable I resolves to 69
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to ABCDE
SYMBOLGEN: Macro variable I resolves to 69
ABCDE 69
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 69
SYMBOLGEN: Macro variable I resolves to 70
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 72) condition is TRUE; loop will iterate again.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
48 The SAS System 07:55 Wednesday, April 7, 2010

SYMBOLGEN: Macro variable CONTSTR resolves to ABCDE
SYMBOLGEN: Macro variable I resolves to 70
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to ABCDEF
SYMBOLGEN: Macro variable I resolves to 70
ABCDEF 70
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 70
SYMBOLGEN: Macro variable I resolves to 71
MLOGIC(CONCAT_CTRL): %DO %WHILE(&i < 72) condition is TRUE; loop will iterate again.
MLOGIC(CONCAT_CTRL): %LET (variable name is CONTSTR)
SYMBOLGEN: Macro variable CONTSTR resolves to ABCDEF
SYMBOLGEN: Macro variable I resolves to 71
MLOGIC(CONCAT_CTRL): %PUT &contstr &i
SYMBOLGEN: Macro variable CONTSTR resolves to ABCDEFG
SYMBOLGEN: Macro variable I resolves to 71
ABCDEFG 71
MLOGIC(CONCAT_CTRL): %LET (variable name is I)
SYMBOLGEN: Macro variable I resolves to 71
SYMBOLGEN: Macro variable I resolves to 72
MLOGIC(CONCAT_CTRL): %DO %WHILE() condition is FALSE; loop will not iterate again.
MLOGIC(CONCAT_CTRL): Ending execution.

Could you please correct me to concatenate the control characters into a string?

Thanks and Regards,
AnjiReddy. Message was edited by: Reddy1729

[ChrisNZ]

It looks like:
1- Macro variables don't like storing non-printable characters.
2- SQL in V9.1 doesn't like a missing 2nd parameter in the compress function
3- Even if the 2nd parameter is not missing, the 3rd parameter 'c' is not honoured in SQL V9.1

So you must store hex values instead of actual values in your macro variable.
You can do this with a data step or with a macro:
[pre]

data _null_;
length TXT $64;
do I=0 to 31;
TXT=put(I,hex2.)||TXT;
end;
call symput('CTRL_CHAR1',TXT);
run;

%macro concat_ctrl;
%global CTRL_CHAR2;
%do i=0 %to 31;
%let CTRL_CHAR2=&CTRL_CHAR2%sysfunc(putn(&i,hex2.));
%end;
%mend;
%concat_ctrl;

ods html;
proc sql; * '21'x is the ! character;
select compress('210A21'x, "&CTRL_CHAR1"x) as GOOD_DATA_VAR from sashelp.class(obs=1);
select compress('210A21'x, "&CTRL_CHAR2"x) as GOOD_MACRO_VAR from sashelp.class(obs=1);
select compress('210A21'x, ' ','c') as BAD_C_PARAM from sashelp.class(obs=1);
quit;
data TEST;
GOOD_C_PARAM=compress('210A21'x, , 'c');
proc print noobs;
run;
ods html close;
[/pre]
yields (note how the non-printable character 0A (in the middle) is still in the 3rd output):
[pre]
GOOD_DATA_VAR
!!

GOOD_MACRO_VAR
!!

BAD_C_PARAM
! !

GOOD_C_PARAM
!!

[/pre]

[deleted_user]

Hi,

Thank a lot for posting the resoltuion to the issue which I have.

I have used the expression compress('Var1',' ','c') in proc sql and verified the data, it eliminates all the control characters. Can I goahead with this?

Kindly advise.

Thanks and Regards,
AnjiReddy.

[ChrisNZ]

If control characters are gone, by all means go ahead. Which version of sas are you using?

[deleted_user]

Hi,

Thanx a ton for your promptive responses.

As you said using compress function with 'c' as the third argument isn't honoured in proc sql. When I verified last time, my test data didn't have special characters, when I included special characters in the input data, I found that it isn't working properly.
so read the data as hexadecimal and invoked the macro function that you specified above and then derived a good macro variable in proc sql as you specified above

Thanks and Regards,
AnjiReddy.