Trailing Space Truncation Issue (substr/tranwrd/praxchange)

[Satish_Parida]

Hi Experts,

We are trying to remove the special pattern from start and end of the strings if present.

In the following code 

options mprint;

libname odmds 'D:\skp\odmds';

%let NON_PRINTABLE_ENCLOSING_CHAR=#**;
%let splln=%length(&NON_PRINTABLE_ENCLOSING_CHAR);
%put &=splln.;

data odmds.test;
infile cards dlm=",";
input ItemData_Value:$100.;
cards;
#**    both leading and laging spaces    #**
#**    only leading spaces#**
#**only laging spaces    #**
;
run;

%macro spl_test;
	data odmds.test1;
	set odmds.test;
	if substr(strip(ItemData_Value),1,&splln) = "&NON_PRINTABLE_ENCLOSING_CHAR." 
		= substr(strip(ItemData_Value),length(strip(ItemData_Value))-&splln.+1,&splln.) then do;
		ItemData_Value1 = substr(ItemData_Value,&splln+1,length(ItemData_Value)-2*&splln);
		ItemData_Value2 = tranwrd(ItemData_Value,"&NON_PRINTABLE_ENCLOSING_CHAR",'');
		*ItemData_Value3 = prxchange('s/^#\*\*|#\*\*$,/', -1, ItemData_Value);	
	end;
	run;
%mend spl_test;
%spl_test;

Issue:

1. ItemData_Value1, ItemData_Value2 are not retaining the trailing spaces how ever the leading spaces are retained.


2. Also if we can get any help on ItemData_Value3 praxchange regex to replace the special character to null.

Thank you in advance.

Note: Kindly use BASE SAS Editor or SAS EG to see the final dataset result.

[Community_Guide]

Hello @Satish_Parida,

Your question requires more details before experts can help. Can you revise your question to include more information? 

Review this checklist:

• Specify a meaningful subject line for your topic.  Avoid generic subjects like "need help," "SAS query," or "urgent."
• When appropriate, provide sample data in text or DATA step format.  See this article for one method you can use.
• If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
  
• It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message.  From there you can adjust the title and add more details to the body of the message.  Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @Kurt_Bremser

.

[ChrisNZ]

Issue:

1. ItemData_Value1, ItemData_Value2 are not retaining the trailing spaces how ever the leading spaces are retained.

2. Also if we can get any help on ItemData_Value3 praxchange regex to replace the special character to null.

1. SAS char variables always retain trailing spaces as they have a fixed length. So the question is unclear.

If you were hoping to shorten the length of the variable, that is not possible.  The text can be shortened, not the variable.

2. As explained, there is no null string in SAS. A string full of spaces is what is called a missing value in SAS, and what is evaluated as null in SQL.

[Kurt_Bremser]

Forget the previous message (Community Guide), I hit the wrong button.

First: DO NOT USE MACRO CODE WHERE IT IS NOT NEEDED!!!!!

It only makes reading the code harder and serves no purpose.

Second, SAS character variables ALWAYS retain trailing spaces, as spaces are used to pad any content that is shorter than the defined variable length. A missing character variable therefore consists of blanks (spaces) only.

Third, you somehow missed to tell us what you really want.

I ran your code with an additional proc print:

%let NON_PRINTABLE_ENCLOSING_CHAR=#**;
%let splln=%length(&NON_PRINTABLE_ENCLOSING_CHAR);
%put &=splln.;

data test;
infile cards dlm=",";
input ItemData_Value:$100.;
cards;
#**    both leading and laging spaces    #**
#**    only leading spaces#**
#**only laging spaces    #**
;
run;


data test1;
set test;
if substr(strip(ItemData_Value),1,&splln) = "&NON_PRINTABLE_ENCLOSING_CHAR." 
	= substr(strip(ItemData_Value),length(strip(ItemData_Value))-&splln.+1,&splln.) then do;
	ItemData_Value1 = substr(ItemData_Value,&splln+1,length(ItemData_Value)-2*&splln);
	ItemData_Value2 = tranwrd(ItemData_Value,"&NON_PRINTABLE_ENCLOSING_CHAR",'');
	*ItemData_Value3 = prxchange('s/^#\*\*|#\*\*$,/', -1, ItemData_Value);	
end;
run;

proc print data=test1;
run;

Result:

Beob.    ItemData_Value                                  ItemData_Value1                       ItemData_Value2

  1      #**    both leading and laging spaces    #**        both leading and laging spaces        both leading and laging spaces
  2      #**    only leading spaces#**                       only leading spaces                   only leading spaces           
  3      #**only laging spaces    #**                    only laging spaces                    only laging spaces                

What should be different?

[Satish_Parida]

@Kurt_Bremser 

I used a macro function and a mprint just to simplify the understanding of the code as macro variables are being used inside the code, please understand this is a code snippet not the whole code. The issue can not be seen if I use a proc print.

@Kurt_Bremser  @ChrisNZ 

I am using a SAS EG to see the output data from the resultant data set.

Issue:

In all the records given in odmds.test dataset, we are trying to remove the '#**' in the start and at the end.

In the process of doing that the substring and the tranwrd function is trimming the trailing spaces from the variable.

We need both leading and trailing spaces of the character string after removing '#**' from the original string.
I was trying to get the desired result using prxchange but I could not get the correct regex for that, if I could get any help on that part it would be helpful too.

P.S. Please use the Base SAS editor or EG to see the output, so that u can see the spaces truncated in the process.

PSS, t is very hard to explain the issue with blank characters, I have tried in this forum earlier and it is very hard to do that. Kindly bear with me.

@ChrisNZ 

I understand SAS keeping a full char length, but when we upload these data to a database, no trailing spaces are loaded for these variables. Please use Base SAS editor or EG to see the output text.

[Kurt_Bremser]

You refuse to understand one of the most basic facts of SAS character variables: they are ALWAYS padded with blanks. ALWAYS. You can never lose "trailing spaces".

You could only lose characters that are not hex 20's, but that is not what you have here.

See this proof:

%let NON_PRINTABLE_ENCLOSING_CHAR=#**;
%let splln=%length(&NON_PRINTABLE_ENCLOSING_CHAR);
%put &=splln.;

data test;
infile cards dlm=",";
input ItemData_Value:$50.;
cards;
#**    both leading and laging spaces    #**
#**    only leading spaces#**
#**only laging spaces    #**
;
run;


data test1;
set test;
if substr(strip(ItemData_Value),1,&splln) = "&NON_PRINTABLE_ENCLOSING_CHAR." 
	= substr(strip(ItemData_Value),length(strip(ItemData_Value))-&splln.+1,&splln.) then do;
	ItemData_Value1 = substr(ItemData_Value,&splln+1,length(ItemData_Value)-2*&splln);
	ItemData_Value2 = tranwrd(ItemData_Value,"&NON_PRINTABLE_ENCLOSING_CHAR",'');
	*ItemData_Value3 = prxchange('s/^#\*\*|#\*\*$,/', -1, ItemData_Value);	
  checkvalue1 = put(ItemData_Value1,$hex100.);
  checkvalue2 = put(ItemData_Value2,$hex100.);
end;
run;

proc print data=test1 noobs;
run;

Result:

ItemData_Value                                  ItemData_Value1                       ItemData_Value2

#**    both leading and laging spaces    #**        both leading and laging spaces        both leading and laging spaces
#**    only leading spaces#**                       only leading spaces                   only leading spaces           
#**only laging spaces    #**                    only laging spaces                    only laging spaces                

                                            checkvalue1

20202020626F7468206C656164696E6720616E64206C6167696E672073706163657320202020202020202020202020202020
202020206F6E6C79206C656164696E6720737061636573202020202020202020202020202020202020202020202020202020
6F6E6C79206C6167696E67207370616365732020202020202020202020202020202020202020202020202020202020202020

                                            checkvalue2

2020202020626F7468206C656164696E6720616E64206C6167696E6720737061636573202020202020202020202020202020
20202020206F6E6C79206C656164696E67207370616365732020202020202020202020202020202020202020202020202020
206F6E6C79206C6167696E672073706163657320202020202020202020202020202020202020202020202020202020202020

You can see the blanks (all the 20's)

[Satish_Parida]

@Kurt_Bremser 

Thank you for explaining me the SAS char variable properties.

The issue is when we insert these data into a Oracle table, I can not see any trailing space in the varchar2 field in the database.

So the API which tries to match the original text and the final text in Oracle fails due to mismatching trailing spaces.

One more thing I saw is, other blank characters such as Tab, LF and CR are not truncated if present at the end of the string.

Finally I was trying to remove the '#**' using a praxchange, if you could help me with it.

[ChrisNZ]

Now that the behaviour of the trailing characters is clarified, here is the regex you might want:

data TEST;
infile cards dlm=",";
input ItemData_Value : $100.;
ItemData_Value3 = prxchange('s/(^#\*\*|#\*\* *$|[[:^print:]])//', -1, ItemData_Value); 
cards;
#**    both leading and laging spaces    #**
#**    only leading spaces#**
#**only laging #**spaces    #**
run;

It removes starting and ending #** as well as non-printable characters.

[Kurt_Bremser]

PS if you want to make the contents of macro variables visible, use options symbolgen. No macro definition needed.

[Kurt_Bremser]

PPS if you want a fixed defined length of character columns in a database, don't use varchar, use char instead.

[Kurt_Bremser]

PPPS EG (and other dataset viewing tools) will always cut trailing blanks, just like a data step does when you are not using formatted output. If you want to mark a certain amount of blanks, you always need a trailing non-blank character. By the definition, SAS can never know how many "visible trailing blanks" you wanted, as it only has trailing blanks.

[Satish_Parida]

@Kurt_Bremser 

So there is no way we can have certain number of trailing spaces for a SAS character variable?

PS, we checked we can have other blank characters(tab, CR, LF) at the end of the string.

[Kurt_Bremser]

Try this:

options symbolgen;

%let NON_PRINTABLE_ENCLOSING_CHAR=#**;
%let splln=%length(&NON_PRINTABLE_ENCLOSING_CHAR);
%put &=splln.;

data test;
infile cards dlm=",";
input ItemData_Value:$50.;
cards;
#**    both leading and laging spaces    #**
#**    only leading spaces#**
#**only laging spaces    #**
;
run;

data test1;
set test;
if
  substr(strip(ItemData_Value),1,&splln) = "&NON_PRINTABLE_ENCLOSING_CHAR." =
  substr(strip(ItemData_Value),length(strip(ItemData_Value))-&splln.+1,&splln.)
then do;
  index = length(ItemData_Value) - 2*&splln + 1;
	ItemData_Value1 = substr(ItemData_Value,&splln+1,length(ItemData_Value)-2*&splln);
  substr(ItemData_Value1,index,1) = '00'x;
  checkvalue1 = put(ItemData_Value1,$hex100.);
end;
index1 = length(ItemData_Value1);
run;

proc print data=test1 noobs;
run;

I set an artificial end-of-string at the initial position of the trailing marker. It may be that this tricks a) SAS into transmitting the correct length and b) Oracle to accept the trailing blanks.

I am not versed in the prx functions, so somebody else needs to help you with that.

[Tom]

Why do you want to store trailing spaces into a VARCHAR() field? 

Why not just store the length in a separate field and upload that along with the strings?

You can then use logic in your external database to append trailing spaces.

But again. Why do you want to store trailing spaces into a VARCHAR() field? 

%let NON_PRINTABLE_ENCLOSING_CHAR=#**;
%let splln=%length(&NON_PRINTABLE_ENCLOSING_CHAR);
%put &=splln.;

data have;
  infile cards truncover ;
  input ItemData_Value $100.;
cards;
data have;
  infile cards truncover ;
  input ItemData_Value $100.;
cards;
#**...no leading or lagging spaces....#**
#**  both leading and lagging spaces  #**
#**                only leading spaces#**
#**only lagging spaces                #**
.
;

data want;
  set have;
  length=lengthn(itemdata_value);
  if length < 2*&splln. then do;
    length=0;
    itemdata_value=' ';
  end;
  else do;
    length=length-2*&splln.;
    itemdata_value=substr(itemdata_value,&splln+1,length);
  end;
run;

proc print;
run;

Obs    ItemData_Value                         length

 1     ...no leading or lagging spaces....      35
 2       both leading and lagging spaces        35
 3                     only leading spaces      35
 4     only lagging spaces                      35
 5                                               0