Remove Precedure_code number in the end of Prededure_Name column?

[ybz12003]

Hello,

I would like to remove the PROCEDURE_CODE number located in the end of the Prededure_Name column.  Be wear that not all the text in the Prededure_Name column carries the PROCEDURE_CODE.  Please let me know how to approach it; thanks

data datain1;
      infile datalines delimiter=',';
  input PROCEDURE_CODE : 10.  PROCEDURE_NAME : $500.  ;
datalines;
99222, INITIAL HOSP.CARE -LEV 2,
102032759, METHYLPREDNI INJ      40.00MG     102032759,
101502000, ASSIST/TEACH(AMBU/BAG) 15MIN      101502000,
;
run;

.

[ballardw]

I think it will help if you describe some rules as to how to recognize the "number" at the end of the text that should be removed versus other things built with digits that may not be the offending number. You example might imply that the number is always exactly 9 digits, possibly starting with "10".

For your example of 3 records this seems to work but is subject to the actual definition of the "number". The starting bit is SCAN with the -1 parameter to get the last "word" in the value. You can comment out the Drop to see the values of W as found.

data datain1;
      infile datalines delimiter=',';
  input PROCEDURE_CODE : 10.  PROCEDURE_NAME : $500.  ;
  length w $ 15;
  w= scan(PROCEDURE_NAME,-1);
  if length(w)= 9 and w=:'10' and input(w,9.) ne .
     then PROCEDURE_NAME = substr(PROCEDURE_NAME,1, index(PROCEDURE_NAME,w)-1);
  drop w;
datalines;
99222, INITIAL HOSP.CARE -LEV 2,
102032759, METHYLPREDNI INJ      40.00MG     102032759,
101502000, ASSIST/TEACH(AMBU/BAG) 15MIN      101502000,
;
run;

[FreelanceReinh · Accepted solution]

Hello @ybz12003,

If the number is always at the end of the name ([edit:] and it should not stay elsewhere), you can safely replace it with a blank using the TRANWRD function. (Otherwise consider using the TRANSTRN function.)

data want;
set datain1;
procedure_name=tranwrd(procedure_name,strip(put(procedure_code,32.)),' ');
run;

[kleelasiva9]

data datain1;
  infile datalines delimiter=',';
  input PROCEDURE_CODE : 10.  PROCEDURE_NAME : $500.  ;
  PROCEDURE=TRANSTRN(PROCEDURE_NAME,put(PROCEDURE_CODE,best.),trimn(" ")) ; /*code*/
datalines;
99222, INITIAL HOSP.CARE -LEV 2,
102032759, METHYLPREDNI INJ      40.00MG     102032759,
101502000, ASSIST/TEACH(AMBU/BAG) 15MIN      101502000,
;
run;