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
;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.