topic Re: Importing CSV tables with dates in SAS Procedures

Importing CSV tables with dates

menhtp — Thu, 30 Oct 2014 19:44:59 GMT

I'm trying to import CSV file with dates . Looks like when I do that some dates are not read correctly in SAS. I suspect the reason

is that some of the dates within csv are in a format e.g text that SAS cannot convert correctly to a sas date. To remedy this, i have read

a document somewhere that using the following expression when importing:

Libname..

Mixed=yes

stringdates=yes

scantime=yes;

and then adding a statement like this

data sheet29 ( drop= datevar)

format datevar date9.;

set .......(rename=(datevar=_datevar)))

_datevar=input ( datevar, mmdyy10.);

run;

Libname .... clear

The problem is how do i integrate this statement with my proc import, which looks like this:

PROC IMPORT OUT= WORK..... DATAFILE= "C.....
DBMS=..... REPLACE;
...............
GETNAMES=YES;

run:

Thanks.

Re: Importing CSV tables with dates

ballardw — Thu, 30 Oct 2014 20:34:53 GMT

Show some of the example data.

Also look in the log after running proc import. There should be code that shows exactly how SAS tried to read the variable in a statement like INFORMAT Datevaraibe xxxxxx.; What format shows where I have the XXXXX?

You may want to increase the guessingrows option for how much of the data to examine when importing to decide what the informat is used.

BTW, if this file comes from an Excel file exported to CSV you may have a mix of dates like 07/21/2014 and 48756 where the second are the native Excel date value. Which SAS cannot tell are dates or get pretty flaky on read.

Re: Importing CSV tables with dates

menhtp — Thu, 30 Oct 2014 21:46:49 GMT

Ballardw said:

You are correct the data comes from EXCEL I convert to CSV before exporting into SAS.

That is correct I think the 48756 dates are the main culprits as they appear different on SAS thus cannot be read correctly.

What is the remedy?

Re: Importing CSV tables with dates

ballardw — Thu, 30 Oct 2014 22:06:43 GMT

In Excel highlight the entire column and the apply the date format to all the cells. Repeat for other date columns. Then do the file-save-as to CSV.

I suspect some folks may have written a VB script to accomplish the cell formatting but I'm not a VB programmer except in desperation.