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;
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?
@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?
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
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?
Thank you for quick solution. your code and @PaigeMiller code both worked great. Thank you both.
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;
It Worked. THANK YOU...
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;
thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.