creating new string variables from a single string variable

[sms1891]

Dear all,

I have a string variable called narrative.  If the variable narrative contains "Indications for Culture" in the text, then I need to create a new variable called reason which captures the text after Indications for Culture:->  till , right before the Testing performed by XYZ (123-456-7890).

NOTE: Indication for culture can appear at any position within the string variable narrative

However, i.e. if the reason is Other (specify) then I need to create another variable Reason_other and need to capture the text after Other Indication:-> till, right before the Testing performed by XYZ (123-456-7890).

If the narrative contains only Other Indication:-> and not Indications for Culture:-> then I need to have Reason_other and need to capture the text after Other Indication:-> till, right before the Testing performed by XYZ (123-456-7890)

I do not want Testing performed by XYZ (123-456-7890)

Thank you so much!

[Reeza]

Is your data already in a SAS data set?

[sms1891]

Yes

[Reeza]

Can you post some of that using the following:

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[Shmuel]

data want;
  set have;
       check1 = "Indications for Culture:-> ";
       len1 = length(check1);
       check2 = "Other Indication:->";
       len2 = length(check2);

       ix1 = index(narrative,check1);
       if ix1 then do;
                reason_length = index(substr(narrative,ix1+len1+1), ','); 
                reason = substr(narrative,ix1+len1+1, reason_length);
       end;

       ix2 = index(narrative,check2);
       if ix2 then do;
                other_length = index(substr(narrative,ix2+len2+1), ','); 
                reason_other = substr(narrative,ix2+len2+1, other_length);
       end;
       drop ix1 ix2 check1 check2 reason_length other_length;
run;     

[sms1891]

Here is the data set:

data WORK.UC_EPIC_TEST;
infile datalines dsd truncover;
input Narrative:$255. ID:$57.;
label Narrative="Narrative";
datalines;
Indications for Culture:->Urology patient,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Recent positive UA,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Recent positive UA,RIGHT FLANK NEPH,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Pregnant patient,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Urology patient,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Other (specify),Other Indication:->indwelling catheter,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Urology patient,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Urology patient,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Received in transport media.,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Other (specify),Other Indication:->fever workup,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Received in transport media.,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Received in transport media.,Testing performed by XYZ (123-456-7890) 200000000
Urine culture reflexed based upon urinalysis results.,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Other (specify),Other Indication:->neutropenic pt,Testing performed by XYZ (123-456-7890) 200000000
renal pelvis,Specimen collected in the operating room.,Testing performed by XYZ (123-456-7890) 123456789
Indications for Culture:->Other (specify),Other Indication:->neutropenic,Testing performed by XYZ (123-456-7890) 123456789
Testing performed by XYZ (123-456-7890) 123456789
;;;;

[ScottBass]

Click @Reeza 's link, read it, and edit your post.

P.S.: I pasted your code into SAS, got an error, and went to the next question.

P.P.S: Investigate SAS' support for regular expressions, as well as researching regular expressions themselves.

[Reeza]

@ScottBass  I think that is the output of the code, but @sms1891 needs to post it into a code window. As posted, it's been garbled, I think, so can't be sure of how to accomplish this because I don't know what the delimiter is in the real data. 

---

@ScottBass wrote:

Click @Reeza 's link, read it, and edit your post.

P.S.: I pasted your code into SAS, got an error, and went to the next question.

P.P.S: Investigate SAS' support for regular expressions, as well as researching regular expressions themselves.

---

[ChrisNZ]

@Reeza 

The code cannot run for other reasons too, like commas in the data and the DSD option being used.

@sms1891 

1.Do not use MS office files.

2.Use use {i} or running-man icon to paste your code. 

3.Test the code that you have pasted (copy the code from the web page).

This works:

data WORK.UC_EPIC_TEST;
  infile datalines pad truncover;
  input NARRATIVE $255. ;
  if index(NARRATIVE,'Indications for Culture:->') then
    REASON=prxchange('s/\A.*Indications for Culture:->(.+),Testing performed by.*\Z/\1/',-1,NARRATIVE);
  if REASON =:'Other (specify)' then REASON2 =substr(REASON,36); 
datalines;
Indications for Culture:->Urology patient,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Recent positive UA,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Recent positive UA,RIGHT FLANK NEPH,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Pregnant patient,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Urology patient,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Other (specify),Other Indication:->indwelling catheter,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Urology patient,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Urology patient,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Received in transport media.,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Other (specify),Other Indication:->fever workup,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Received in transport media.,Testing performed by XYZ (123-456-7890) 200000000
Testing performed by XYZ (123-456-7890) 200000000
Received in transport media.,Testing performed by XYZ (123-456-7890) 200000000
Urine culture reflexed based upon urinalysis results.,Testing performed by XYZ (123-456-7890) 200000000
Indications for Culture:->Other (specify),Other Indication:->neutropenic pt,Testing performed by XYZ (123-456-7890) 200000000
renal pelvis,Specimen collected in the operating room.,Testing performed by XYZ (123-456-7890) 123456789
Indications for Culture:->Other (specify),Other Indication:->neutropenic,Testing performed by XYZ (123-456-7890) 123456789
Testing performed by XYZ (123-456-7890) 123456789
run;

REASON	REASON2
Urology patient
Recent positive UA
Recent positive UA,RIGHT FLANK NEPH
Pregnant patient
Urology patient
Other (specify),Other Indication:->indwelling catheter	indwelling catheter
Urology patient
Urology patient
Other (specify),Other Indication:->fever workup	fever workup
Other (specify),Other Indication:->neutropenic pt	neutropenic pt
Other (specify),Other Indication:->neutropenic	neutropenic

[sms1891]

data WORK.UC_

Data work.Uc_Test1;
Set work.Uc_Test;
*Identifying isolated cultures;
if Narrative =: 'Indications for Culture' or Accession =: 'Indications for Culture' then Isolated_Uc = 1;
else Isolated_Uc = 0;

if Narrative =: 'Urine culture reflexed based upon urinalysis results' or Accession =: 'Urine culture reflexed based upon urinalysis results' then UAReflexUc = 1;
else UAReflexUc = 0;

*Reason for isolated urince culture;
if Isolated_Uc = 1 then Reason_Isolated_Uc = scan(Narrative, 2, ";,");
*Reason for isolated urince culture;
if Isolated_Uc = 1 then Reason_Oth_Iso = scan(Narrative, 3, ";,,");
run;

TEST; infile datalines dsd truncover; input Narrative:$255.; label Narrative="Narrative"; datalines; right renal pelvis,Specimen collected in the operating room.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Add to most recent UA.,Indications for Culture:->Recent positive UA,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) After catheter change,Indications for Culture:->Recent positive UA,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) After foley changed,Indications for Culture:->Recent positive UA,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) BEFORE STARTING ABX,Indications for Culture:->Other (specify),Other Indication:->Neutropenic fever,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Before discharge,Indications for Culture:->Urology patient,Specimen received in a sterile container.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Bladder urine for routine culture,Specimen collected in the operating room.,Specimen received in a sterile container.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Bladder urine for routine culture,Specimen collected in the operating room.,Specimen received in a sterile container.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Bladder urine for routine culture,Specimen collected in the operating room.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Catheter placed 8/5. Please send from catheter specimen.,Indications for Culture:->Recent positive UA,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Clean catch after Foley removed,Indications for Culture:->Recent positive UA,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Collect from Foley,Indications for Culture:->Urology patient,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Collected at time of insertion of new foley,Indications for Culture:->Urology patient,Specimen received in a sterile container.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Collected directly for Foley replacement,May use urine already collected if available.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Draw prior to antibiotics given,Indications for Culture:->Recent positive UA,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) From LEFT PCN,Indications for Culture:->Urology patient,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) From RIGHT PCN,Indications for Culture:->Urology patient,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) From new catheter,Indications for Culture:->Recent positive UA,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) He actually does need a culture, specimen from this AM was rejected due to suspected contamination,Indications for Culture:->Recent positive UA,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) If urine protein creatinine ratio not collected, please send along with urine culture,Indications for Culture:->Recent positive UA,Specimen received in a sterile container.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->+ blood cultures,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->? bacteremia,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI bundle,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI in neutropenic patient,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI in renal allograft,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI,Specimen received in a sterile container.,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AKI,Testing performed by Aaaaaa-Bbbbbb Cccccccc Dddddddddddd Eeeeeeeeee (111-222-3333) Indications for Culture:->Other (specify),Other Indication:->AMS, hypotension,Specimen received in a sterile container. ;;;; 

Thank you for all who responded. I am attaching the SAS dataset.  I tried the following code but I am not able to get the Other Indication reasonn if the Indication for Culture is Other (specify).

[ChrisNZ]

1. You code doesn;t run 2. What's wrong with the proposed methods?

[Shmuel · Accepted solution]

I tried to run your data and decided:

1) to save data as .txt file - see attached file test8.txt

2) define delimiter as <line feed> '012'x;

3) fixed my posted code

You are invited to test it. Please post details in case of issues.

It seems to me that next code works fine: 

filename test '/folders/myfolders/myfolders/flat/test8.txt';
data TEST; 
infile test dsd truncover dlm='12'x; 
input Narrative:$255.; 
label Narrative="Narrative"; 
run;

data want;
  set test;
       check1 = 'Indications for Culture:->';
       len1 = length(check1);
       check2 = 'Other Indication:->';
       len2 = length(check2);

       ix1 = index(narrative,check1); put _N_ ix1=;
       if ix1 then do;
          desc = check1;
          reason_length = index(substr(narrative,ix1+len1+1), ','); 
          reason = substr(narrative,ix1+len1, reason_length);
       end;

       ix2 = index(narrative,check2); put _N_ ix2=;
       if ix2 then do;
          desc = check2;
          other_length = index(substr(narrative,ix2+len2+1), ','); 
          reason_other = substr(narrative,ix2+len2, other_length);
       end;     
       if not missing(desc);  
       keep desc reason reason_other;
run;