sas infile csv date in quote

Lussy04

2 min read

·

23-01-2025

1

0

Share

Reading CSV files into SAS is a common task, but complications can arise when dealing with dates enclosed in quotation marks. This article will guide you through effectively importing CSV data with dates formatted this way, using the INFILE statement and various SAS date functions. We'll cover different scenarios and provide solutions to ensure accurate data import.

Understanding the Problem: Dates in Quotes

CSV files often represent dates as text strings, particularly when exported from other applications. These strings may be enclosed in double quotes, causing issues when directly importing into SAS using the default INFILE options. SAS's standard date import routines might misinterpret quoted dates as text, leading to inaccurate analysis and reporting.

The INFILE Statement and Key Options

The core of importing CSV data into SAS lies within the INFILE statement. Several options within this statement are crucial for managing quoted dates:

• DSD (Delimiter-Separated Data): This option specifies that your data is delimited, most commonly by commas. The DSD option is essential for CSV files.

• QUOTE="": This crucial option tells SAS what character encloses quoted strings. In standard CSV, this is a double quote ("). Without this option, SAS might treat parts of the date string incorrectly.

• MISSOVER: This option handles missing values gracefully. When a field is empty, MISSOVER moves to the next field, preventing errors.

Example: Importing CSV with Quoted Dates

Let's consider a CSV file named mydata.csv with a date column:

"Date","Value"
"01/01/2024",10
"02/15/2024",20
"03/10/2024",30
" ",40  

Here's a SAS program to import this data, correctly handling the quoted dates:

proc import datafile="mydata.csv" 
	out=work.mydata
	dbms=csv 
	replace;
	getnames=yes;
	datarow=2;
	guessingrows=max;
    quotedate='"';
run;

data mydata2;
	set work.mydata;
	format Date date9.;  *Applying a date format*
	if Date = " " then Date = .; *Handling missing dates*
run;

proc print data=mydata2;
run;

Explanation:

1. The proc import statement uses dbms=csv to specify a CSV file. quotedate='"'; tells SAS that dates are quoted using double quotes.
2. The data step applies the date9. format to explicitly convert the imported date column to a SAS date value. This is crucial for date-based calculations and analysis.
3. Missing values (empty quotes) are handled to avoid errors.

Different Date Formats and Input Statements

The above example assumes a specific date format (MM/DD/YYYY). You may encounter various formats like DD/MM/YYYY, YYYY-MM-DD, etc. SAS provides flexible input functions to handle these:

• INPUT function with date informats: The INPUT function allows you to specify the informat to parse the date string. For example:

data mydata2;
  set work.mydata;
  Date2 = input(Date,mmddyy10.);  * Using mmddyy10. informat *
  format Date2 date9.;
run;

Remember to choose the correct informat (mmddyy10., ddmmyy10., yyyymmdd10., etc.) matching your date format in the CSV.

Error Handling and Data Validation

Robust data import involves error handling. Check for invalid dates using the validdate() function:

data mydata2;
	set work.mydata;
	Date_num = input(Date,mmddyy10.);
	if validdate(Date_num) then do;
		format Date_num date9.;
	end;
	else do;
		Date_num = .; *Set to missing if invalid*
	end;
run;

This code checks the validity of the parsed date; invalid dates are marked as missing.

Conclusion: Clean and Reliable Date Import

Successfully importing CSV data with quoted dates requires careful use of the INFILE statement options and appropriate date informats. Remember to always check your data for errors and missing values after import. By following the techniques outlined here, you ensure accurate and reliable data for your SAS analysis. Understanding date formats, informats and error handling will significantly improve the reliability of your SAS data imports.