How do i extract value from these possible value from a column?

[imdickson]

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?

[andreas_lds]

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.

[DanielLangley]

@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.

[Shmuel]

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,'/');

[FreelanceReinh]

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;

[Kurt_Bremser]

@FreelanceReinh : Nice!

[Kurt_Bremser]

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

[Ksharp · Accepted solution]

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;

[FreelanceReinh]

@Ksharp: Elegant! (As long as no embedded blanks break it.)

data have;
input h $12.;
cards;
132/break it
;