Hi everyone. I have a column that contains below possible value
132/11
132/33
33/11
11/
33/
33
11
I want to do the following
if whatever behind / is not blank, take the value after /
If value after / is blank, take the front
if no /, take whatever it has.
I tried substr(colA,1,length(colA)-1) and it wont work.
Can anyone help me on this?
data have;
input h $;
cards;
132/11
132/33
33/11
11/
33/
33
11
;
run;
data want;
set have;
want=scan(h,-1,'/ ');
run;
Posting data in usable form helps us to help you.
i would use a regular expression: "/(.*\/)?(.+)/" and the function prxposn to get the second capture group.
@andreas_ldsThat looks very nearly perfect. I would suggest the expression "/(.*\/)?([0-9]+)/" instead to avoid capturing the end slash if there is no data after the slash (capture 11 instead of 11/).
132/11
132/33
33/11
11/
33/
33
11
@imdicksonI would suggest a website like https://regex101.com/ to test and learn regular expressions.
Alternatively use next code:
ix = index(var,'/');
if ix > 0 and ix < length(trim(var))
then new_var = substr(var,ix+1);
else new_var=compress(var,'/');
Hi @imdickson,
The combination of COALESCEC and SCAN functions can do this as well:
data have;
input h $;
cards;
132/11
132/33
33/11
11/
33/
33
11
;
data want;
set have;
length w $8;
w=coalescec(scan(h,2,'/'),scan(h,1,'/'));
run;
Using the "standard" string handling functions:
data have;
input h $;
cards;
132/11
132/33
33/11
11/
33/
33
11
;
run;
data want;
set have;
if countw(h,'/') <= 1
then w = h;
else do;
w = scan(h,2,'/');
if w = ' ' then w = scan(h,1,'/');
end;
run;
proc print data=want noobs;
run;
Result:
h w 132/11 11 132/33 33 33/11 11 11/ 11 33/ 33 33 33 11 11
data have;
input h $;
cards;
132/11
132/33
33/11
11/
33/
33
11
;
run;
data want;
set have;
want=scan(h,-1,'/ ');
run;
@Ksharp: Elegant! (As long as no embedded blanks break it.)
data have;
input h $12.;
cards;
132/break it
;
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.