topic Re: Extract only some characters from a string in SAS Programming

Extract only some characters from a string

David_Billa — Wed, 12 May 2021 05:32:13 GMT

I want to extract only some characters from a string, but I'm not getting the function to get it done. Assume I've a following values in one variable.

27183_ASD_271835678_CDNTN_0004_DE_2020-09_

37184_ASD_ASG_371845679_CDSNN_0004_DN_2020-09_

22186_ASD_ASD_ASH_221865778_CKNNN_0004_CT_2020-09_

Desired result is,

271835678

371845679

221865778

One hint which I can say is, I want to identify the second occurance of first five characters seperated by underscore.

Re: Extract only some characters from a string

japelin — Wed, 12 May 2021 05:57:22 GMT

try this code.

data have;
  length char $200;
  input char;
datalines;
27183_ASD_271835678_CDNTN_0004_DE_2020-09_
37184_ASD_ASG_371845679_CDSNN_0004_DN_2020-09_
22186_ASD_ASD_ASH_221865778_CKNNN_0004_CT_2020-09_
;
run;

data want;
  set have;
  length get $200;
  cnt=countw(char,'_');
  do i=2 to cnt;
    get=scan(char,i,'_');
    if INDEX(get,trim(scan(char,1,'_')))=1 then output;
  end;
  keep get;
run;

Re: Extract only some characters from a string

ChrisNZ — Wed, 12 May 2021 06:59:36 GMT

So you want to keep 9 or more consecutive digits?

This should do it:

VAR1 = prxchange('/.*?(\d{9,}).*/\1/', -1, VAR);

Re: Extract only some characters from a string

David_Billa — Wed, 12 May 2021 07:41:09 GMT

It's not working. Can we do this with other function instead of PRXCHANGE?

26         data want;
27         set have;
28         CHAR1 = prxchange('/.*?(\d{9,}).*/\1/', -1, CHAR);
29         run;

ERROR: Invalid characters "\1/" after end delimiter "/" of regular expression "/.*?(\d{9,}).*/\1/".
ERROR: The regular expression passed to the function PRXCHANGE contains a syntax error.

Re: Extract only some characters from a string

David_Billa — Wed, 12 May 2021 07:44:00 GMT

Thanks! Can we also do this without do loop as I want to incorporate this logic in DI Studio expression?

Re: Extract only some characters from a string

ChrisNZ — Wed, 12 May 2021 07:53:25 GMT

Oh I forgot the s

data T;
  VAR1 = prxchange('s/.*(\d{9,}).*/\1/', -1, '27183_ASD_271835678_CDNTN_0004_DE_2020-09_');
  put VAR1=;
run;

VAR1=271835678

Re: Extract only some characters from a string

japelin — Wed, 12 May 2021 07:59:58 GMT

I don't know if this will fit in DI Studio, but if you want to extract without using do loop, there is a way to use substr. How about this?

data want;
  set have;
  length keyword get $200;
  keyword=substr(char,1,index(char,'_')-1);
  pos1=find(char,trim(keyword),2);
  pos2=find(char,'_',pos1);
  get=substr(char,pos1,pos2-pos1);
  drop pos1 pos2;
run;

Re: Extract only some characters from a string

FreelanceReinh — Wed, 12 May 2021 08:23:58 GMT

Hi @David_Billa,

@David_Billa wrote:

Thanks! Can we also do this without do loop as I want to incorporate this logic in DI Studio expression?

data want;
set have;
want=scan(char,countc(substr(char,1,find(char,substr(char,1,5),6)-1),'_')+1,'_');
run;

Re: Extract only some characters from a string

Ksharp — Wed, 12 May 2021 12:02:05 GMT

data have;
  length char $200;
  input char;
datalines;
27183_ASD_271835678_CDNTN_0004_DE_2020-09_
37184_ASD_ASG_371845679_CDSNN_0004_DN_2020-09_
22186_ASD_ASD_ASH_221865778_CKNNN_0004_CT_2020-09_
;
run;
data want;
 set have;
 want=scan(substr(char,prxmatch('/\d{9,}/',char)),1,'_');
run;