sas infile csv pass through colon in quote

Lussy04

2 min read

·

23-01-2025

1

0

Share

Importing CSV data into SAS can present challenges when dealing with colons (:) within fields enclosed in double quotes. Standard INFILE statements might misinterpret these colons as data delimiters, leading to data corruption. This article explains how to correctly handle such situations using the PASS THROUGH option within the INFILE statement.

Understanding the Problem

CSV files use commas as delimiters, separating values within a row. However, when a field contains a comma, it's typically enclosed in double quotes to prevent misinterpretation. Similarly, if a field contains a colon and the colon is part of the data itself (not a delimiter), it must be within quotes. The challenge arises when SAS's default behavior encounters a colon within a quoted field and how to instruct SAS to treat it as part of the data and not as a field separator.

The Solution: PASS THROUGH

The PASS THROUGH option within the INFILE statement is the key to correctly importing CSV data with colons within quoted fields. This option instructs SAS to ignore the data delimiter (comma in this case) within quoted fields and treat the entire quoted string as a single value.

proc import datafile="my_data.csv" 
  out=my_sas_data 
  dbms=csv 
  replace;
  getnames=yes;
  delimiter=',';
  infile "my_data.csv" passthrough;
run;

This code snippet demonstrates how to use PASS THROUGH. Notice that the PASS THROUGH option is added within the INFILE statement itself. This effectively tells SAS to treat everything within the double quotes as a single data element, regardless of the presence of colons or other delimiters.

Explanation:

• proc import: This statement initiates the data import process.
• datafile="my_data.csv": Specifies the path to your CSV file.
• out=my_sas_data: Assigns the imported data to a SAS dataset named my_sas_data.
• dbms=csv: Indicates that the data source is a CSV file.
• replace: Overwrites the existing my_sas_data dataset if it already exists.
• getnames=yes: Automatically assigns variable names from the CSV header row.
• delimiter=',': Specifies the comma (,) as the field delimiter.
• infile "my_data.csv" passthrough;: This is crucial. The infile statement within proc import utilizes passthrough to address colons within quoted fields. This is sometimes needed to reinforce the comma as the delimiter, despite the presence of colons within the quotes.

Example CSV Data

Let's consider a CSV file (my_data.csv) with the following data:

"Name","Address","City:State"
"John Doe","123 Main St","Anytown:CA"
"Jane Smith","456 Oak Ave","Springfield:IL"

Without the PASS THROUGH option, SAS might split "City:State" into two variables due to the colon. With PASS THROUGH, it correctly treats "Anytown:CA" and "Springfield:IL" as single values within the "City:State" variable.

Alternative Approaches (Less Recommended)

While PASS THROUGH is the most efficient solution, some might consider pre-processing the CSV file to replace colons with another character before importing. However, this adds an extra step and increases complexity. It’s generally best to let SAS handle this using PASS THROUGH.

Conclusion

Effectively importing CSV data with colons in quoted fields requires using the PASS THROUGH option within the SAS INFILE statement. This ensures that SAS correctly interprets and imports data, preventing data loss or corruption. Remember to use this option when necessary to ensure the accuracy of your data import process. Using PASS THROUGH in conjunction with proc import provides a robust and efficient solution. Always double-check your imported data to ensure that the values are accurately reflected.