topic Re: How to remove a Character at the end of the string Based on Criteria in SAS Programming

How to remove a Character at the end of the string Based on Criteria

SASuserlot — Tue, 28 Nov 2023 15:08:51 GMT

I have the following scenario where I need to remove the '-' from the end of the string.

1. If the 'reason' (VARIABLE) is total text, then KEEP '-' at the end of the string.

2. If the 'reason' (VARIABLE) is a partial date with '-' at the end, then delete the '-' at the end of the string.

3. If the 'reason' (VARIABLE) is complete date without '-' at the end keep the text as it is.

Have data:

Want:

1. didnot go to store

2. 2017-20

3. 2017-21-01

Thank you for your suggestions and time

data have;
 reason = 'didnot go to store-';
 output;
 reason = '2017-20-';
 output;
 reason = '2017-21-01';
 output;
 run;

Re: How to remove a Character at the end of the string Based on Criteria

PaigeMiller — Tue, 28 Nov 2023 15:33:23 GMT

@SASuserlot wrote:

1. If the 'reason' (VARIABLE) is total text, then KEEP '-' at the end of the string.

Want:

1. didnot go to store

These two are contradictory. Which do you want, keep the '-' at the end of the string, or remove as you indicate in "Want"?

Also, what counts as a partial date? Is 01JAN- a partial date? What should we do about text like 'purchase $12.79' which isn't what you describe as 'total text', and is not a date or partial date?

Re: How to remove a Character at the end of the string Based on Criteria

SASuserlot — Tue, 28 Nov 2023 15:56:39 GMT

Thank you for your questions.

That's the tricky part of the data at this moment I have.

If the text contains 'Alphabetical characters'. then Keep '-'.

If the text contains only dates in the character string ( contains ONLY numerical and '-'). If any character date didn't have the year, month, and date (2018-01-, 2018-), it was considered a partial date. Also, your example is regarded as a partial date. For the time being, it's mandatory to have the 'Year' in my data.

Your case " 'purchase $12.79" we keep '-'.

To make it easier to understand. If the variable contains only 'numeric' and '-' in text, then delete '-' at the end; otherwise, keep it. Please let me know if I missed any scenario

Re: How to remove a Character at the end of the string Based on Criteria

A_Kh — Tue, 28 Nov 2023 16:06:25 GMT

data have;
 reason = 'didnot go to store-';
 output;
 reason = '2017-20-';
 output;
 reason = '2017-21-01';
 output;
 reason= '17-NOV-';
 output;

data want;
 	set have;
	if substr(reason, length(reason), 1)= '-' then do;
		if anyalpha(reason) eq 0 then reason=substr(reason, 1, length(reason)-1);
	end; 
proc print;run;

Does this work the way you want?

Re: How to remove a Character at the end of the string Based on Criteria

PaigeMiller — Tue, 28 Nov 2023 16:09:51 GMT

If the variable contains only 'numeric' and '-' in text, then delete '-' at the end; otherwise, keep it. Please let me know if I missed any scenario

Here is how I would program this:

data want;
    set have;
    if anyalpha(reason)=0 /* No alphabetical characters */ 
        /* does it contain - */ and find(reason,'-')>0 
        /* Is last character - */ and reverse(trim(reason))=:'-'
        then /* Remove - at end */ reason=substr(reason,1,length(reason)-1);
run;

Re: How to remove a Character at the end of the string Based on Criteria

SASuserlot — Tue, 28 Nov 2023 16:12:24 GMT

Thank you for quick solution. your code and @PaigeMiller code both worked great. Thank you both.

Re: How to remove a Character at the end of the string Based on Criteria

SASuserlot — Tue, 28 Nov 2023 16:12:52 GMT

It Worked. THANK YOU...

Re: How to remove a Character at the end of the string Based on Criteria

Ksharp — Wed, 29 Nov 2023 05:45:44 GMT

data have;
 reason = 'didnot go to store-';
 output;
 reason = '2017-20-';
 output;
 reason = '2017-21-01';
 output;
 run;
data want;
 set have;
 want=prxchange('s/(\d)\-+$/\1/',1,strip(reason));
run;

Re: How to remove a Character at the end of the string Based on Criteria

SASuserlot — Mon, 04 Dec 2023 15:47:04 GMT

thank you