BookmarkSubscribeRSS Feed
menhtp
Calcite | Level 5

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.

3 REPLIES 3
ballardw
Super User

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.

menhtp
Calcite | Level 5

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?

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3388 views
  • 0 likes
  • 2 in conversation