topic Oracle REGEXP_SUBSTR in SAS in SAS Programming

Oracle REGEXP_SUBSTR in SAS

SASAna — Wed, 11 Nov 2020 13:14:08 GMT

Hi SAS users,

I have data coming like this in procedure code field - 1234|2345|4567 for a claim. I want to change make this to multiple lines by splitting the procedure codes pipes.

Original data :

clm procedure_code

1 1234|2345|4567

Needed format :

clm procedure_code

1 1234

1 2345

1 4567

Thanks,

Deepa

Re: Oracle REGEXP_SUBSTR in SAS

PeterClemmensen — Wed, 11 Nov 2020 13:21:06 GMT

data have;
input clm procedure_code $30.;
datalines;
1 1234|2345|4567 
;

data want;
   set have;
   do i = 1 to countw(procedure_code, '|');
      p_code = scan(procedure_code, i, '|');
      output;
   end;
   drop procedure_code i;
run;

Re: Oracle REGEXP_SUBSTR in SAS

CarmineVerrell — Wed, 11 Nov 2020 13:27:55 GMT

Hi Deepa,
Here is a simple solution example if the data that you mentioned is static and always starts and stops in the same position within the original data in procedure_code. If it doesn't then we would need to modify the code to make it more flexible.

data orgdata;
infile cards;
input clm pro_code $14.;

cards;
1 1234|2345|4567
;
run;

Data newfmt(drop=pro_code);
set orgdata;
procedure_code=substr(pro_code,1,4);
output;
procedure_code=substr(pro_code,6,4);
output;
procedure_code=substr(pro_code,11,4);
output;
run;