keep all char after specific position of hyphen

[sasecn]

This should be a simple question. I have a string, e.g a variable called "v1" that has value of "11-abc-def-1234-q111". I want to just keep all after the third hyphen. So, the result should be "1234-q111". I used scan(v1, 4, '-') it just gave me "1234". I tried to use catx (scan(v1, 4, '-'), scan(v1, 5, '-')). I still cannot get the result wanted. I felt it is about the position index i used, but not sure. Any help would be appreciated.

Thanks.

[data_null__]

Similar to what you tried with SCAN but using CALL SCAN to find the position of the 4th word.

45         data _null_;
46            x="11-abc-def-1234-q111";
47            call scan(x,4,p,_n_);
48            s=substr(x,p);
49            put _all_;
50            run;

x=11-abc-def-1234-q111 p=12 s=1234-q111 _ERROR_=0 _N_=4

[sasecn]

Thanks for your reply. I am using the variable "v1" in proc sql to create anther variable which is the substred value. Would call scan work in proc sql?

[data_null__]

I would look at the documentation if I were you.

---

@sasecn wrote:

Thanks for your reply. I am using the variable "v1" in proc sql to create anther variable which is the substred value. Would call scan work in proc sql?

---

[s_lassen · Accepted solution]

You cannot use CALL routines in SQL. What you can do is use PROC FCMP to write a function that calls the routine:

data have;
  v1="11-abc-def-1234-q111";
run;

options cmplib=work.funcs;
proc fcmp outlib=work.funcs.strings;
  function remain_str(string $,n,delimiters $) $200;
    p=0;
    len=0;
    call scan(string,n,p,len,delimiters);
    if p>0 then
      return(substr(string,p));
    else return ('');
    endfunc;
run;
proc sql;
  select v1,remain_str(v1,4,'-') as result from have;
quit;

[FreelanceReinh]

Hello @sasecn,

---

@sasecn wrote:

I tried to use catx (scan(v1, 4, '-'), scan(v1, 5, '-')).

---

You just forgot the first argument of the CATX function, the delimiter:

catx('-', scan(v1, 4, '-'), scan(v1, 5, '-'))

If the number of hyphens in V1 was not limited to four, you could nest SUBSTRN and FIND function calls, but make sure that at least three hyphens are present. In PROC SQL:

select v1, case when countc(v1,'-')>=3
                then substrn(v1,find(v1,'-',find(v1,'-',find(v1,'-')+1)+1)+1)
                else ' '
           end as want