Help using Base SAS procedures

Importing CSV tables with dates

Reply
Occasional Contributor
Posts: 13

Importing CSV tables with dates

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.

Super User
Posts: 11,343

Re: Importing CSV tables with dates

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.

Occasional Contributor
Posts: 13

Re: Importing CSV tables with dates

Ballardw said:


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.


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?

Super User
Posts: 11,343

Re: Importing CSV tables with dates

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.

Ask a Question
Discussion stats
  • 3 replies
  • 2210 views
  • 0 likes
  • 2 in conversation