sas infile csv date in double quote

3 min read 23-01-2025

Many CSV files contain dates enclosed in double quotes. This can complicate data import into SAS. This article explains how to efficiently handle such situations using the INFILE statement's options. We'll cover various scenarios and best practices to ensure accurate and robust data ingestion.

Understanding the Challenge: Dates in Quotes

CSV files, while simple in structure, can present challenges when dates are enclosed in double quotes. Standard SAS INFILE procedures might misinterpret the quoted dates, leading to errors or data type mismatches. This is because SAS, by default, treats the double quotes as part of the string value.

Solution: Utilizing INFILE Statement Options

The key to handling quoted dates in SAS is to leverage the INFILE statement's options to control how SAS interprets the data. The most important options in this case are:

DSD (Double Quotes): This option instructs SAS to treat double quotes as delimiters for string values, allowing us to correctly parse the date fields.
FIRSTOBS=n: This lets you specify which row to begin reading from (useful for skipping headers).
MISSOVER: This option helps handle missing values gracefully. When SAS encounters an invalid date format it will read the next observation.

Example Scenarios and Code

Let's explore different scenarios and the corresponding SAS code:

Scenario 1: Simple Date Format

Assume your CSV file (mydata.csv) has a header row and a "Date" column with dates enclosed in double quotes (e.g., "01/15/2024").

proc import datafile="mydata.csv" 
  out=mydata 
  dbms=csv 
  replace;
  getnames=yes;
  datarow=2; /* Skip header row */
run;

data mydata_formatted;
  set mydata;
  Date_Formatted = input(compress(Date,"\""), mmddyy10.); /* Remove quotes and convert */
  format Date_Formatted date9.;
run;

This code first imports the data using proc import. Then, we use a data step to remove the double quotes using the compress function and convert it to a SAS date value using the input function with the appropriate informat (mmddyy10.). Finally, it formats the resulting date.

Scenario 2: Different Date Formats

If your dates are in a different format (e.g., "2024-01-15"), adjust the informat accordingly.

data mydata_formatted;
  set mydata;
  Date_Formatted = input(compress(Date,"\""), yymmdd10.); 
  format Date_Formatted date9.;
run;

Scenario 3: Handling Missing Values

If some date entries might be missing or invalid, use the MISSOVER option within the infile statement for more robust data import. This will continue reading the rest of the data when an error is encountered. This is crucial for large datasets.

proc import datafile="mydata.csv" 
  out=mydata 
  dbms=csv 
  replace;
  getnames=yes;
  datarow=2;
run;

data mydata_formatted;
  infile 'mydata.csv' dsd missOver firstobs=2;
  input @1 Date :$20. other_variables; /* Adjust variables as needed */
  Date_Formatted = input(compress(Date,"\""), mmddyy10.);
  format Date_Formatted date9.;
run;

This revised approach uses infile directly within the data step with missOver. We also specify the exact column width for the date using the :$20. format. Remember to adjust this value to accommodate the length of your date strings.

Best Practices

Inspect your data: Always examine your CSV file before writing your SAS code. Note the date format, the presence of headers, and any potential missing values.
Use appropriate informats: Choose the correct informat (mmddyy10., yymmdd10., date9., etc.) that matches your date format.
Error handling: Incorporate error handling (e.g., checking for invalid dates) to avoid unexpected results. The MISSOVER option is a valuable tool here.
Data validation: After importing, always validate your data to ensure accuracy.

By following these steps and adapting the code to your specific CSV file structure, you can confidently import CSV data with dates enclosed in double quotes using SAS. Remember to replace "mydata.csv" with the actual path to your file and adjust the variable names and informats to match your data.