Oracle REGEXP_SUBSTR in SAS

[SASAna]

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

[PeterClemmensen]

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;

[CarmineVerrell]

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;