topic Re: How do i extract value from these possible value from a column? in SAS Programming

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

imdickson — Fri, 22 Mar 2019 08:33:22 GMT

Hi everyone. I have a column that contains below possible value

132/11

132/33

33/11

11/

33/

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?

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

andreas_lds — Fri, 22 Mar 2019 08:50:51 GMT

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.

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

DanielLangley — Fri, 22 Mar 2019 09:15:21 GMT

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

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

Shmuel — Fri, 22 Mar 2019 09:36:35 GMT

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

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

FreelanceReinh — Fri, 22 Mar 2019 09:38:37 GMT

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;

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

Kurt_Bremser — Fri, 22 Mar 2019 09:54:22 GMT

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

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

Kurt_Bremser — Fri, 22 Mar 2019 09:55:21 GMT

@FreelanceReinh : Nice!

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

Ksharp — Fri, 22 Mar 2019 11:09:30 GMT

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;

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

FreelanceReinh — Fri, 22 Mar 2019 13:40:42 GMT

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

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