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!
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;
Yes
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...
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;
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
;;;;
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.
@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.
The code cannot run for other reasons too, like commas in the data and the DSD option being used.
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 |
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).
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.