Problem Importing Dates from CSV File

Reply
New Contributor
Posts: 4

Problem Importing Dates from CSV File

I am attempting to import a CSV file into SAS Studio, however all dates (formatted as 9/11/2012 in the csv) are being converted to the single digit numbers. If I cut and insert a row at the top of the data then it imports it properly. Please help, I don't understand why having the second row (1st row has my variable names) without a date causes this error. Thanks!

 

Here is the code for my import:

%web_drop_table(WORK.IMPORT);
FILENAME REFFILE '/folders/myfolders/DP_NUTR_BMI-REQUESTED-20180227-1.csv';
PROC IMPORT DATAFILE=REFFILE
DBMS=CSV
OUT=WORK.IMPORT;
GETNAMES=YES;

RUN;
PROC CONTENTS DATA=WORK.IMPORT; RUN;
%web_open_table(WORK.IMPORT);

Super User
Posts: 23,776

Re: Problem Importing Dates from CSV File

What is the type and format of the date variable you think is being read in correctly?

Post the output from the PROC CONTENTS if you can. 

 

If it's number then it may be correct and you may need to apply a format.

 

data import_with_date;
set import;

format DATE_VARIABLE yymmdd10.;

run;

@Lucas1 wrote:

I am attempting to import a CSV file into SAS Studio, however all dates (formatted as 9/11/2012 in the csv) are being converted to the single digit numbers. If I cut and insert a row at the top of the data then it imports it properly. Please help, I don't understand why having the second row (1st row has my variable names) without a date causes this error. Thanks!

 

Here is the code for my import:

%web_drop_table(WORK.IMPORT);
FILENAME REFFILE '/folders/myfolders/DP_NUTR_BMI-REQUESTED-20180227-1.csv';
PROC IMPORT DATAFILE=REFFILE
DBMS=CSV
OUT=WORK.IMPORT;
GETNAMES=YES;

RUN;
PROC CONTENTS DATA=WORK.IMPORT; RUN;
%web_open_table(WORK.IMPORT);


 

New Contributor
Posts: 4

Re: Problem Importing Dates from CSV File

SAS Studio

4
DECEASED_DATEChar1$1.$1.

 

When I run the coding you provide this is the error message I get:

SAS Studio

65 format DECEASED_DATE mmddyy10.;
_________
484
NOTE 484-185: Format $MMDDYY was not found or could not be loaded.
 
Coding used:
data import_with_date;
set import;
format DECEASED_DATE mmddyy10.;
run;
Super User
Posts: 10,280

Re: Problem Importing Dates from CSV File

The best way to read a csv file into SAS is a data step written according to the specification accompanying the file.

Copy the data step code from the log of the successful test, and use it after adapting.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 4

Re: Problem Importing Dates from CSV File

Posted in reply to KurtBremser

Could you elaborate on the "specification accompanying the file"? Not sure if I understand you completely.

 

The only difference from the successful import and unsuccessful is in the CSV file where I put a date in the first row of data. 

 

Thanks,

Lucas

Super User
Posts: 10,280

Re: Problem Importing Dates from CSV File

A data file without a description of column attributes is mostly useless for meaningful work, so you should resort to the description you got with the file.

As a helper, copy the data step code from the log of the successful run of proc import with the manipulated data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 4

Re: Problem Importing Dates from CSV File

Posted in reply to KurtBremser

Is this what you're referring to?

 

SAS Studio

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat1
DECEASED_DATENum8MMDDYY10.MMDDYY10.
Super User
Posts: 10,280

Re: Problem Importing Dates from CSV File

Copy the data step code from the log. I won't write that a fourth time.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 8,127

Re: Problem Importing Dates from CSV File


@Lucas1 wrote:

Could you elaborate on the "specification accompanying the file"? Not sure if I understand you completely.

 

The only difference from the successful import and unsuccessful is in the CSV file where I put a date in the first row of data. 

 

Thanks,

Lucas


A CSV file does not supply any metadata data (other than the optional header row) about the data that it contains.

If someone sends you a CSV file they should also supply you with information that clearly describes what each column contains.  Without that you will need to guess at the meaning type and length for each column.

 

All proc import is doing is guessing what is in the file and then generating a data step to read it. 

 

If you know what is in the file then just write the data step yourself.  Even if you don't know what is in the file you should still be able to do a much better job than PROC IMPORT can at guessing what is in the file .

 

Ask a Question
Discussion stats
  • 8 replies
  • 83 views
  • 0 likes
  • 4 in conversation