Truncated strings after using concatenate

[Michelle Debbink]

Hi All -

I am hoping to get some input on a concatenated strings issue I can't seem to figure out. Using STATA MP 14.2, fully updated.

My data are patient-level data, but include the text of operative notes from patient charts, and the original file is .xlsx. Because of size limitations, the operative notes spilled over into additional rows. The person who created the dataset was lovely and each row repeats the patient's information with an identifier for LINE which identifies which part of the operative note. I don't know which program was used to create the original text of the operative notes.

Example data:
PatientID EncounterID NoteID LINE NOTE_TEXT
XXXX YYYY ZZZZ 1 Blah blah blah
XXXX YYYY ZZZZ 2 More of the same note
XXXX YYYY AAAA 1 New operative note during same hospital stay
XXXX YYYY AAAA 2 that keeps going into this line...

Occasionally, a patient will go to the OR more than once during the same hospital stay (encounter ID), and so there may be two NoteIDs, with multiple values of LINE for each NoteID. Some patients also have more than one encounter in the system over the time during which we have taken our data.

WIth STATAs new-ish strL function, I was very much hoping I could get all of the note text for each op note into one cell for each patient...

I started by reshaping the data to wide based on Note ID so that for each noteID, i have the text of that note in NoteText1, NoteText2, NoteText3, etc.
This all seems to work fine. The issue comes when I try to concatenate the Note text one to another and get them all into one cell. For some reason, the string data are truncated at what seem to be very random points in the concatenation. There is no set string length at which the concatenation ends, nor is it a function of only getting through two cells and then stopping. The truncation occurs in the middle of cells for nearly every single instance of truncation that I have gone through. After concatenation, the finished string lengths vary from 300s - 8700, so there does not seem to be a rhyme or reason to the truncation.

A couple problems I tried to address:
1) The NoteText variable string texts have multiple carriage returns and/or line breaks because they were directly copied from the chart. I have performed the concatenation with and without removing these using the various string trim functions (both unicode and standard). The results are the same whether carriage returns have been removed or not (that is, the string ends abruptly at the exact same point regardless of whether these extra characters are removed). Side note: ideally I could keep the carriage returns, because I'd like to output these op notes back to a text file for chart review purposes...

2) Creation of a strL variable before vs during: I know that STATA is automatically supposed to bump strings "to the next level" if the string created exceeds the set limit. When initially creating the variable, I just generated it from the gen or egen command. When that didn't work, I tried to create an empty strL variable and use the replace command. Again, the results are identical, and strings are truncated at seemingly random points

3) Use of concat vs var+ var+ var: I have tried both thetyped out gen (or replace) varname = notetext1 + notetext2 + notetext3... and also egen varname = concat(notetext*). Again, both approaches result in the same outcome.

4) .xlsx vs delimited .txt files. I have been importing the original data from Excel because it seems to handle it correctly. I tried importing as tab-delimited .txt thinking this might address it, but I get jumbled data; even when specifying tab rather than automatic delimiter, each carriage return in the op note becomes a new observation/row.

I guess the question is, what might I be missing that could be causing the strings to truncate rather than continuing the concatenation in strL variables?

The code I've tried is below.

Thanks!
Michelle



reshape wide NOTE_TEXT PROVIDER CONTACT_SERIAL_NUM, i(NOTE_ID) j(LINE)
(note: j = 1 2 3 4 5 6)

Data long -> wide
-----------------------------------------------------------------------------
Number of obs. 8048 -> 3182
Number of variables 10 -> 24
j variable (6 values) LINE -> (dropped)
xij variables:
NOTE_TEXT -> NOTE_TEXT1 NOTE_TEXT2 ... NOTE_TEXT6
PROVIDER -> PROVIDER1 PROVIDER2 ... PROVIDER6
CONTACT_SERIAL_NUM -> CONTACT_SERIAL_NUM1 CONTACT_SERIAL_NUM2 ... CONTACT_SERIAL_NUM6
-----------------------------------------------------------------------------

egen fullnote = concat(NOTE_TEXT*)
(3 missing values generated)

drop fullnote

gen strL fullnote = ""

replace fullnote = (NOTE_TEXT1 + NOTE_TEXT2 + ...NOTE_TEXT6)
(3 missing values generated)

[Joseph Coveney]

I'm not aware of any ASCII character or Unicode character or character combination that would cause truncation when concatenating string variables in Stata, but you might want to look at the characters at which truncation occurs and see what characters follow them in the unconcatenated cells. You could try reversing the strings (strreverse()) and see whether truncation still occurs and if so whether it occurs at the same characters or the same locations (string lengths).

How are you aware of when the truncation occurs? For example, does the following yield the same truncations as you get after -reshape-? (I'm assuming that whoever prepared the Excel workbook didn't break the notes up in the middle of a word, but rather on the nearest space less than or equal to some threshold string length.)

generate int cell_len = strlen(NOTE_TEXT)
quietly {
    bysort NOTE_ID (LINE): replace NOTE_TEXT = NOTE_TEXT[_n-1] + " " + NOTE_TEXT if _n > 1
    by NOTE_ID: replace cell_len = sum(cell_len)
    by NOTE_ID: keep if _n == _N
    replace NOTE_TEXT = stritrim(NOTE_TEXT)
}
list if strlen(NOTE_ID) < cell_len

The various string trim functions (both unicode and standard) won't remove carriage return-line feed characters.

[William Lisowski]

I feel I must ask, only because you are going from Excel to Stata: can you confirm that after importing the Excel spreadsheet into Stata, the data was imported faithfully? That is, when viewed from with Stata, using list or the data browser, the contents of the strings, immediately after the import, were represented correctly, especially those strings that were later the location of the truncation?

Implicit in the output of help data type is that strings (but not strLs) cannot contain the character represented by a binary 0. I haven't done any experimenting, but if somehow the data in Excel came in with that character, you might have this sort of problem - or not, I don't really know any more than that the binary 0 character is not allowed.

Again, I don't expect this question to lead to anything, but feel it must be asked just to explicitly rule out this sort of problem.

[Michelle Debbink]

Thanks for the question, William - I forgot to mention that I did check that the strings are imported faithfully from excel. The entire text is present, though I am not sure if there are any binary 0s present. If so, they are being randomly placed, as the truncation points even occur in the middle of words....

Thanks!
Michelle

[Michelle Debbink]

Apologies, Joseph - I just saw your reply as well. I became aware of the truncation as I was double checking a few of the notes to ensure they were complete, and they were not. I used the character function to remove carriage returns and line feed (char 13 and 10 I believe?) and that did remove the line breaks when browsing through the data.

The strings are truncated quite abruptly, often in the middle of words or in the middle of sentences in the middle of paragraphs. It doesn't seem to be associated with the line breaks or with any particular letter. I will try that reversal test and see if the truncation point is the same. I will also try your code - you're correct that the person who created my Excel file did not cut the text in the middle of words!

Thanks for the help!

Michelle

[Michelle Debbink]

Both options - reversing the text and using the code Joseph supplied - result in the same truncations. The string lengths end up being equal in all circumstances...

Thanks again for any thoughts.
Michelle

[William Lisowski]

At this point, I think we need to see a reproducible example. Here's how I would produce one.

1. Find a PatientID with just one note, and it's a note that gets truncated during concatenation, and the note does not have privacy issues (that is, it's a note whose content you are comfortable sharing). Let's call that PatientID GNXL
2. Import the data from Excel
3. Reduce the data in Stata to that to just the one example
   keep if PatientID=="GNXL"
4. If there are privacy issues with variables other than NOTE_TEXT, replace the relevant variables with similar made-up values, as you did in post #1 above.
5. Save what you now have as a new Stata dataset
   save small.dta
6. Quit Stata, relaunch Stata, and
   use small.dta
   describe
7. Concatenate the NOTE_TEXT values into a new strL variable using whatever approach you prefer
8. Confirm that the concatenated value is again truncated using the list command.

Now, select and copy everything that's in your Results window starting with the use command and ending with the output of the list command, and paste it into a new post on this topic. And while we normally encourage the use of the dataex command from SSC for uploading sample data, in this case I'd recommend you upload small.dta itself, since lurking in the back of everyone's mind is the possibility of something strange in your data.

To assure maximum readability of results that you post, please paste the results copied from the Results window into a code block in the Forum editor, as explained in section 12 of the Statalist FAQ linked to at the top of the page. For example, the following:

[code]
. sysuse auto, clear
(1978 Automobile Data)

. describe make price

storage display value
variable name type format label variable label
-----------------------------------------------------------------
make str18 %-18s Make and Model
price int %8.0gc Price
[/code]

will be presented in the post as the following:

. sysuse auto, clear
(1978 Automobile Data)

. describe make price

              storage   display    value
variable name   type    format     label      variable label
-----------------------------------------------------------------
make            str18   %-18s                 Make and Model
price           int     %8.0gc                Price

What a lot of tedious work, but at this point I think the "introspection" approach has failed to pay off (although perhaps Monday morning you'll get new readers with better introspection that that of those of us playing on Sunday), and you are going to have to do the hard work of peeling away extraneous stuff to produce a reproducible example we can play with to diagnose.

[Michelle Debbink]

Thanks, William - I have tried a few more things, but agree that none of my diagnostic approaches seem to be working. I have created the dataset you suggested and will post my data and results in a new thread.

Best,
Michelle

[Michelle Debbink]

Hi All - below is the output of the small.dta. Though I'm using the code block, things don't seem to be lining up super well, so I apologize for any problems with readability. I also tried several ways to make the list output more readable, but it really is too long, so I first did the standard list output for all variables, which truncates the strings by default. I then performed list, notrim for each variable NOTE_TEXT1, NOTE_TEXT2, and fullnote separately. Also, I left the return carats and whitespace in the results intact in case there is a clue there that I am not seeing. Interestingly, though I used the notrim option for the list output, the output for the fullnote variable is truncated at an earlier point than the text truncates when I use browse and look directly at the data contained in the cell. It does not go a lot further, but does have a few more lines in the cell than are present in the output in the results window.

I am having trouble attaching the small.dta file (getting an error about it not being an image file?) so I've got it linked here at my Google Drive. For completeness' sake I've also uploaded the same de-identified Excel file here in case anyone wants to look at the original data.

Thanks for any insight you might have.
Michelle

. use small.dta

. describe

Contains data from small.dta
  obs:               2                          
 vars:               5                          17 Jul 2017 10:27
 size:         4,108                          
-------------------------------------------------------------------------------------------------
                        storage      display    value
variable name     type           format     label      variable label
-------------------------------------------------------------------------------------------------
PAT_NAME         str34           %34s                   PAT_NAME
PROVIDER         str39           %39s                   PROVIDER
NOTE_ID            long         %10.0g                   NOTE_ID
LINE                    byte         %10.0g                  LINE
NOTE_TEXT      str1976    %1976s                  NOTE_TEXT
-------------------------------------------------------------------------------------------------
Sorted by: 

. reshape wide NOTE_TEXT, i(NOTE_ID) j(LINE)
(note: j = 1 2)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                   2   ->       1
Number of variables           5   ->       5
j variable (2 values)            LINE   ->   (dropped)
xij variables:                       NOTE_TEXT   ->   NOTE_TEXT1 NOTE_TEXT2
-----------------------------------------------------------------------------

. order PAT_NAME PROVIDER NOTE_ID NOTE_TEXT1 NOTE_TEXT2

. egen fullnote = concat(NOTE_TEXT*)

. list

     +--------------------------------------------------------------------------------------------------------------------------------+
  1. |       PAT_NAME          |          PROVIDER     |            NOTE_ID                                                       |
     |         Test patient          |          Doctor X          |          5.837e+08                                                       |
     |----------------------------------------------------------------------------------------------------------------------------------|
     | NOTE_TEXT1                                                                                                                                    |
     | PREOPERATIVE DIAGNOSES: Pregnancy, desire for repeat cesarean section, desire for perm.. |
     |----------------------------------------------------------------------------------------------------------------------------------|
     | NOTE_TEXT2                                                                                                                                    |
     | Kiwi. One pop-off did occur. Two pulls were attempted. The rest of the fetus was deliv..                  |
     |----------------------------------------------------------------------------------------------------------------------------------|
     | fullnote                                                                                                                                               |
     | PREOPERATIVE DIAGNOSES: Pregnancy, desire for repeat cesarean section, desire for perm.. |
     +---------------------------------------------------------------------------------------------------------------------------------+

. describe

Contains data
 obs:             1                          
 vars:             6                          
 size:         7,334                          
-------------------------------------------------------------------------------------------------
              storage   display    value
variable name             type            format     label   variable label
-------------------------------------------------------------------------------------------------
PAT_NAME                str34            %34s                PAT_NAME
PROVIDER                str39            %39s                PROVIDER
NOTE_ID                   long          %10.0g                NOTE_ID
NOTE_TEXT1           str1976     %1976s               1 NOTE_TEXT
NOTE_TEXT2           str1976     %1976s               2 NOTE_TEXT
fullnote                       strL                %9s                   
-------------------------------------------------------------------------------------------------
Sorted by: NOTE_ID
     Note: Dataset has changed since last saved.

. list NOTE_TEXT1, notrim
>                                                                                               
>                                                         NOTE_TEXT1  
  1.   PREOPERATIVE DIAGNOSES: Pregnancy, desire for repeat cesarean section, desire for permanen
>t sterilization.



POSTOPERATIVE DIAGNOSES: Same as above.



PROCEDURES: Low transverse repea
> t cesarean section, tubal ligation.



PRIMARY SURGEON: Doctor X, MD



ASSISTING SURGEON:Docto
> r Y, MD



RESIDENT: Doctor Z, MD/BA



ANESTHESIA: Spinal.



EBL: 800 cc.



URINE OUTPUT: 20
> 0 cc.



FLUIDS: 2.5.



DRAINS: Foley.



COMPLICATIONS: None.



SPECIMENS REMOVED: Placenta,
>  bilateral fallopian tubes.



FINDINGS: Normal tubes, ovaries, uterus. See MiChart for Apgars 
> and weight.



INDICATION: A 27-year-old G4, P3 at 39 and 1 who presents for a repeat LTCS. Pat
> ient has a history of gestational hypertension as well.



CONDITION: Stable to recovery.



PL
> AN: Admit to inpatient.



After verifying informed consent, the patient was taken back to the 
> operating room where patient, procedure, and site were verified. The spinal anesthesia was admi
> nistered, and the patient was placed in dorsal lithotomy with a leftward tilt. The patient was 
> then prepped and draped in the usual sterile fashion. An incision was made using a scalpel thro
> ugh the skin and carried down to the underlying fascia. The fascia was then scored bilaterally.
>  The fascial incision was then extended laterally using the Bovie as well as a hemostat to elev
> ate the fascia from the underlying rectus muscles. The fascia was then elevated using Kocher cl
> amps x2, and the rectus muscle was easily dissected off the overlying fascia from the superior 
> and inferior edge. The peritoneal cavity was then entered using a hemostat and then extended bl
> untly. The omentum was packed using a moist lap. The bladder flap was created using Metzenbaum 
> scissors as well as pickups. The bladder blade was then inserted into the pelvis, and a low tra
> nsverse uterine incision was made. The fetus was delivered using a  

. list NOTE_TEXT2, notrim

                                                                                                 
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                NOTE_TEXT2  
  1.   Kiwi. One pop-off did occur. Two pulls were attempted. The rest of the fetus was delivered
>  without difficulty. The uterine incision was closed in a running fashion using 0 Vicryl. An im
> bricating layer was not completed. Attention was then drawn to both tubes. A clear spot was not
> ed in the mesosalpinx, and a small incision was made using the Bovie. Two silk ties were passed
>  through this hole and were used to tie down two ends of the tubes. The tube was then cut, and 
> the specimen was taken to Pathology. This was repeated on the other side. The uterus was then e
> xamined and noted to be hemostatic. Both fallopian tube sites were noted to be hemostatic. The 
> moist lap was removed. The uterus was then put back into the abdomen. We decided not to irrigat
> e. At this time, Dr. X scrubbed out, and Dr. Y entered the room and scrubbed in. The fascia was
>  closed using 0 PDS in a running fashion. The subcutaneous tissue was irrigated, and hemostasis
>  was achieved using cautery. The skin was closed using 0 Monocryl in a subcuticular fashion. Th
> e patient was taken back to the recovery room. She was in stable condition. Sponge, lap, and in
> strument counts were correct x2 at the end of the procedure.



I was present for delivery of t
> he infant, repair of the uterine incision, and the tubal ligation. Dr. Y was present for closur
> e of the fascia and skin.  

.  list fullnote, notrim

                                                                                                 
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                                                                
>                                                fullnote  
  1.   PREOPERATIVE DIAGNOSES: Pregnancy, desire for repeat cesarean section, desire for permanen
> t sterilization.



POSTOPERATIVE DIAGNOSES: Same as above.



PROCEDURES: Low transverse repea
> t cesarean section, tubal ligation.



PRIMARY SURGEON: Doctor X, MD



ASSISTING SURGEON:Docto
> r Y, MD



RESIDENT: Doctor Z, MD/BA



ANESTHESIA: Spinal.



EBL: 800 cc.



URINE OUTPUT: 20
> 0 cc.



FLUIDS: 2.5.



DRAINS: Foley.



COMPLICATIONS: None.



SPECIMENS REMOVED: Placenta,
>  bilateral fallopian tubes.



FINDINGS: Normal tubes, ovaries, uterus. See MiChart for Apgars 
> and weight.



INDICATION: A 27-year-old G4, P3 at 39 and 1 who presents for a repeat LTCS. Pat
> ient has a history of gestational hypertension as well.



CONDITION: Stable to recovery.



PL
> AN: Admit to inpatient.



After verifying informed consent, the patient was taken back to the 
> operating room where patient, procedure, and site were verified. The spinal anesthesia was admi
> nistered, and the patient was placed in dorsal lithotomy with a leftward tilt. The patient was 
> then prepped and draped in the usual sterile fashion. An incision was made using a scalpel thro
> ugh the skin and carried down to the underlying fascia. The fascia was then scored bilaterally.
>  The fascial incision was then extended laterally using the Bovie as well as a hemostat to elev
> ate the fascia from the underlying rectus muscles. The fascia was then elevated using Kocher cl
> amps x2, and the rectus muscle was easily dissected off the overlying fascia from the superior 
> and inferior edge. The peritoneal cavity was then entered using a hemostat and then extended bl
> untly. The omentum was packed using a moist lap. The bladder flap was created using Metzenbaum 
> scissors as well as pickups. The bladder blade was then inserted into the pelvis, and a low tra
> nsverse uterine incision was made. The fetus was delivered using aKiwi. One pop-off did occur. 
> Two pulls were attempted. The rest of the fetus was delivered without difficulty. The uterine i
> ncision was closed in a running fashion using 0 Vicryl.

[William Lisowski]

Thank you for the well-done example. I can't explain why the code delimiters didn't keep your output aligned properly, but it wasn't an issue.

The problem is not that the concatenated string is being truncated. The problem is that the list command (and apparently the browse command, although I didn't look thoroughly at that) are failing to show the entire string, as the different results from list and browse hinted to us.

Using the display command reveals the entire contents of fullnote. I also used outfile to save the value of fullnote to disk as text. This too verified that the concatenation had occurred successfully. I examined the text file using operating system utilities and could not find any unwanted characters that might have impeded correct display.

If this doesn't cause someone to be able to explain what is going on with list, your should submit your example to Stata Technical Support, perhaps referencing this topic.

But if nothing else, you've demonstrated that concatenation does work, and the result - replete with return and newline characters - can indeed be saved to a text file using the outfile command for subsequent review. That's a solution to your problem, if not an explanation of its cause.

Here is what I did, duplicating your approach, using Stata 15.0 for Mac.

cls
use "small.dta", clear
reshape wide NOTE_TEXT, i(NOTE_ID) j(LINE)
order PAT_NAME PROVIDER NOTE_ID NOTE_TEXT1 NOTE_TEXT2
egen fullnote = concat(NOTE_TEXT*)
display fullnote
outfile fullnote using "small.txt", replace wide
type "small.txt"

Here is the output of the display command; the output of the type command is similar, except the string is surrounded by quotation marks.

. display fullnote
PREOPERATIVE DIAGNOSES: Pregnancy, desire for repeat cesarean section, desire for permanent ster
> ilization.



POSTOPERATIVE DIAGNOSES: Same as above.



PROCEDURES: Low transverse repeat cesarean section, tubal ligation.



PRIMARY SURGEON: Doctor X, MD



ASSISTING SURGEON:Doctor Y, MD



RESIDENT: Doctor Z, MD/BA



ANESTHESIA: Spinal.



EBL: 800 cc.



URINE OUTPUT: 200 cc.



FLUIDS: 2.5.



DRAINS: Foley.



COMPLICATIONS: None.



SPECIMENS REMOVED: Placenta, bilateral fallopian tubes.



FINDINGS: Normal tubes, ovaries, uterus. See MiChart for Apgars and weight.



INDICATION: A 27-year-old G4, P3 at 39 and 1 who presents for a repeat LTCS. Patient has a histo
> ry of gestational hypertension as well.



CONDITION: Stable to recovery.



PLAN: Admit to inpatient.



After verifying informed consent, the patient was taken back to the operating room where patient
> , procedure, and site were verified. The spinal anesthesia was administered, and the patient w
> as placed in dorsal lithotomy with a leftward tilt. The patient was then prepped and draped in
>  the usual sterile fashion. An incision was made using a scalpel through the skin and carried 
> down to the underlying fascia. The fascia was then scored bilaterally. The fascial incision wa
> s then extended laterally using the Bovie as well as a hemostat to elevate the fascia from the
>  underlying rectus muscles. The fascia was then elevated using Kocher clamps x2, and the rectu
> s muscle was easily dissected off the overlying fascia from the superior and inferior edge. Th
> e peritoneal cavity was then entered using a hemostat and then extended bluntly. The omentum w
> as packed using a moist lap. The bladder flap was created using Metzenbaum scissors as well as
>  pickups. The bladder blade was then inserted into the pelvis, and a low transverse uterine in
> cision was made. The fetus was delivered using aKiwi. One pop-off did occur. Two pulls were at
> tempted. The rest of the fetus was delivered without difficulty. The uterine incision was clos
> ed in a running fashion using 0 Vicryl. An imbricating layer was not completed. Attention was 
> then drawn to both tubes. A clear spot was noted in the mesosalpinx, and a small incision was 
> made using the Bovie. Two silk ties were passed through this hole and were used to tie down tw
> o ends of the tubes. The tube was then cut, and the specimen was taken to Pathology. This was 
> repeated on the other side. The uterus was then examined and noted to be hemostatic. Both fall
> opian tube sites were noted to be hemostatic. The moist lap was removed. The uterus was then p
> ut back into the abdomen. We decided not to irrigate. At this time, Dr. X scrubbed out, and Dr
> . Y entered the room and scrubbed in. The fascia was closed using 0 PDS in a running fashion. 
> The subcutaneous tissue was irrigated, and hemostasis was achieved using cautery. The skin was
>  closed using 0 Monocryl in a subcuticular fashion. The patient was taken back to the recovery
>  room. She was in stable condition. Sponge, lap, and instrument counts were correct x2 at the 
> end of the procedure.



I was present for delivery of the infant, repair of the uterine incision, and the tubal ligation
> . Dr. Y was present for closure of the fascia and skin.

[Michelle Debbink]

Excellent news - thank you! I was starting to wonder if something like this was going on, but couldn't prove it. I should have tried outfile to see what it would do.

Best,
Michelle