Column shift due to unbalanced quotes

[Yadlapalli]

I recently encountered an issue in a source extract file where two records contained unbalanced double quotes within a character field. As a result, the delimiter parsing shifted columns for those records, causing data to be read incorrectly.

I attempted to develop a SAS validation step to identify records with unbalanced double quotes before the file is processed. However, I have not found a reliable built-in SAS option or function that can consistently detect these issues in a delimited text file particularly when the file contains multiple blank fields and there are no fixed or consistently populated columns that can be used to reliably identify column shifts caused by unbalanced quotation marks

Could you please advise on the following?

1. Is there a recommended SAS method to detect records containing unbalanced quotation marks in a delimited file?
2. Are there any DATA step options, INFILE options, validation utilities, or SAS-provided macros that can identify malformed quoted strings?
3. Does SAS provide any functionality similar to a file integrity check for CSV or delimited files where unmatched quotes may cause column shifts?
4. If no standard solution exists, is there an enhancement request or best practice that SAS recommends for this type of validation?

For context, the issue resulted in column misalignment because a quoted text value was not properly terminated, causing subsequent delimiters to be interpreted as part of the same field.

Any guidance or recommended approach would be greatly appreciated.

[Tom]

The SAS data step has many features to help you debug this type of file.

You can use the LIST statement to dump the lines from the file to the SAS log so you can SEE what you are dealing with.

You can reference the _INFILE_ automatic variable to allow you to run test against the current line (assuming the lines are less than 32K bytes long).  So you could for example use the COUNTW() function to see how many values are on a given line and make sure that each line has the same number of values.

One simple idea to start with would be to use this %CSV2DS() macro which has a number of enhancements over the defaults of PROC IMPORT. (See this thread https://communities.sas.com/t5/SAS-Programming/Reading-delimited-text-files/m-p/775298 )  One of the things that macro will do is setup a view that has each value as a separate observation.

data _values_(keep=row varnum length short) / view=_values_;
...

It uses this to gather the statistics it uses to base its guesses about how to read the variable in and whether to attach a format.

One enhancement that I asked SAS for years (perhaps decades) ago is support for Unix style escape characters in delimited files.  They never did anything about it but now that new versions of SAS allow you to use PROC PYTHON you could probably call some python delimited file testing program from within SAS.

[Ksharp]

That would be very helpful if you could post some real or dummy data and the desired output.

This issue I think is vey easy to address by SAS.

Here is an example:

data _null_;
infile cards truncover ;
input;
if mod(countc(_infile_,'"'),2)=1 then put "Found unbanlanced double quote:" _infile_;
cards;
1,"sdee,ere","sdfpereu"
2,"sdfsds","sdsds"sdfsf"
3,sdfsd"sdsds,"sdfsds"
4,sdsdfs,"dssds,"
;

1    data _null_;
2    infile cards truncover ;
3    input;
4    if mod(countc(_infile_,'"'),2)=1 then put "Found unbanlanced double quote:" _infile_;
5    cards;

Found unbanlanced double quote:2,"sdfsds","sdsds"sdfsf"
Found unbanlanced double quote:3,sdfsd"sdsds,"sdfsds"

[Tom]

It is hard to write a program for all possible invalidly formatted delimited files.

For ones what are just clearly messed up the details of how they are messed up matter. For example why are the quotes unbalanced?  Did they not add quotes around the values that contain quotes?  Did they not double up the embedded quotes so that they could be distinguished from the quotes that surround a value?

But there are a number of delimited files that are well defined, but just don't follow SAS's rules.  For those if you can recognize them you can adjust for them.

For example SAS does not support end-of-line indicators that are embedded inside a value, even if the value is quoted.  For those you need to pre-process the file to remove/replace the end-of-line indicators.  Here is a macro that does that.   %replace_crlf() 

It works by counted the number of quotes to tell whether the end of line characters (carriage return or linefeed) appear inside quotes and then replaces those characters. 

Also SAS does not handle files that use an escape character (typically a backslash) to allow delimiters or quotes inside values.  If such values already have quotes around them then you can just replace them.  So replace \" with "" and \, with , and then SAS will be able to parse the file properly.  If there are not quotes around the values then replace the \" and \, with some other strings that do not appear in your file.  You can then read the resulting file normally.  Just remember to convert your replacement strings back to the intended value.

For more specific help with your file post examples of the lines that are poorly formed.