topic Re: Problem Importing Dates from CSV File in SAS Programming

Problem Importing Dates from CSV File

Lucas1 — Wed, 04 Jul 2018 19:13:06 GMT

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);

Re: Problem Importing Dates from CSV File

Reeza — Wed, 04 Jul 2018 19:18:39 GMT

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);

Re: Problem Importing Dates from CSV File

Kurt_Bremser — Wed, 04 Jul 2018 19:19:19 GMT

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.

Re: Problem Importing Dates from CSV File

Lucas1 — Wed, 04 Jul 2018 19:22:57 GMT

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

Re: Problem Importing Dates from CSV File

Kurt_Bremser — Wed, 04 Jul 2018 19:29:22 GMT

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.

Re: Problem Importing Dates from CSV File

Lucas1 — Wed, 04 Jul 2018 19:33:39 GMT

SAS Studio

DECEASED_DATE

Char

$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;

Re: Problem Importing Dates from CSV File

Lucas1 — Wed, 04 Jul 2018 19:38:13 GMT

Is this what you're referring to?

SAS Studio

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat1

DECEASED_DATE

Num

MMDDYY10.

Re: Problem Importing Dates from CSV File

Kurt_Bremser — Wed, 04 Jul 2018 20:19:32 GMT

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

Re: Problem Importing Dates from CSV File

Tom — Thu, 05 Jul 2018 13:22:37 GMT

@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 .