GREP-like function to retrieve text in SAS

user_3316305 0
Viewed 1316

I want to retrieve specific text within a column in a SAS file.

The file would like the following:

Patient    Location    infoTxt
001        B           Admission Code: 123456 X
                       Exit Code: 98765W
002        C           Admission Code: 4567 WY
                       Exit Code: 76543Z
003        D           Admission Code: 67890 L
                       Exit Code: 4321Z

I want to retrieve just the information after the colon for Admission Code and Exit Code and put them in their own columns. The 'codes' can be any combination of letters, numbers, and blank spaces. The new data would look like the following:

Patient    Location    AdmissionCode      ExitCode
001        B           123456 X            8765W
002        C           4567 WY             76543Z
003        D           67890 L             4321Z

I'm not familiar with the functions in SAS, but maybe the logic would look something like the following:

data want;
  set have;
  do i = 1 to dim(infoTxt)

    AdmissionCode = substring(string1, regexpr(":", string) + 1);
    ExitCode = substring(string2, regexpr(":", string) + 1);

run;

In the code above, string1 would represent the first line of text in infoTxt and string2 would represent the second line of text infoTxt.

sas substring string
4 Answers

SAS can utilize Perl regular expressions through the family of functions that start with PRX. The tip sheet is a great summary if you are familiar with regular expressions.

PRXMATCH and PRXPOSN can test a regex pattern with capture groups and retrieve the group text.

data have;
input;
text = _infile_;
datalines;
Admission Code: 123456 X Exit Code: 98765W
Admission Code: 4567 WY Exit Code: 76543Z
Admission Code: 67890 L Exit Code: 4321Z
run;

data want;
  set have;

  if _n_ = 1 then do;
    retain rx;
    rx = prxparse ('/Admission Code: (.*)Exit Code:(.*)/');
  end;

  length AdmissionCode ExitCode $50;

  if prxmatch(rx,text) then do;
    AdmissionCode = prxposn(rx, 1, text);
    ExitCode = prxposn(rx, 2, text);
  end;

  drop rx;
run;
user_12499620

I like a RegEX with a capture buffer as much as the next guy but you could also use input statement features to read this data.

data info;
   infile cards n=2 firstobs=2;
   input #1 patient:$3. location :$1. @'Admission Code: ' AdmissionCode &$16. #2 @'Exit Code: ' ExitCode &$16.;
   cards;
Patient    Location    infoTxt
001        B           Admission Code: 123456 X
                       Exit Code: 98765W
002        C           Admission Code: 4567 WY
                       Exit Code: 76543Z
003        D           Admission Code: 67890 L
                       Exit Code: 4321Z
;;;;
   run;
proc print;
   run;

enter image description here

user_21962200

There may be a solution out there that does it all in one data step. This creates two steps to deal with the admission and exit being on different rows-- first a data step, then a join to get it back together.

SAS does have regex syntax but I used SAS character functions instead. substr has 3 arguments, string, start position, and end position-- but end position is optional and I've omitted it to tell it to grab everything after the start position. retain is used to fill in the patient and location in the second row of each group.

data admission exit;
    set grep;
    retain patient2 location2;
    if patient ne '' then do; 
        patient2=patient;
        location2=location;
        admissioncode=substr(infoTxt,find(infoTxt,":")+2);
        output admission;
        end;
    else do;
        exitcode=substr(infoTxt,find(infoTxt,":")+2);
        output exit;
        end;
run;
proc sql;
    create table dat as select a.patient2 as patient,a.location2 as location,a.admissioncode,b.exitcode
        from admission a
        left join exit b on a.patient2=b.patient2 and a.location2=b.location2
    ;
quit;
user_57284930

Provided that you always have the same pattern of colons and line breaks, I think you can do this with scan:

  admission_code = scan(infoTxt, 2, '3A0A0D'x);
  exit_code = scan(infoTxt, 4, '3A0A0D'x);

This uses the hex literal '3A0A0D'x to specify :, line feed and carriage return as delimiters for the scan function.

user_6674890
Related