topic Using infile but all the dates are missing in SAS Programming

Using infile but all the dates are missing

LaiQ — Thu, 12 May 2016 10:24:38 GMT

Hi guys,

I am trying to import a csv file which is delimited by '|' and my dates are all in this format "2015-10-31 00:00".

Hence, I code using this:

data want;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'File path' delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat PostDate ANYDTDTM32. ;
informat OpenDate ANYDTDTM32. ;

format PostDate DATE9. ;
format OpenDate DATETIME16. ;

input
PostDate
OpenDate
;  
if _ERROR_ then call symput('_EFIERR_',1);  /* set ERROR detection macro variable */
run;

All my other fields are importing correctly but only the dates are impacted.

So for the PostDate, I do not want the time, but for the OpenDate, I will want the time.

I have compared the number of variables but all tallied up.

So I'm really confused about this problem and there are no error messages in the Log to indicate what is the error.

If I'm using PROC IMPORT, it is fine. But there are empty date fields, which will make it Character instead of Numeric. Hence, I choose to use infile for importing the file.

Appreciate if anyone can help me!

Re: Using infile but all the dates are missing

FreelanceReinh — Thu, 12 May 2016 10:35:05 GMT

Hi @LaiQ,

To extract the date part of a datetime value by means of an informat, you should use the ANYDTDTEw. informat:

informat PostDate ANYDTDTE. ;

What is your problem with OpenDate, if any?

Re: Using infile but all the dates are missing

LaiQ — Thu, 12 May 2016 10:40:47 GMT

Ooh, I have changed it. But all my dates are still missing... The OpenDate is the same format as my PostDate. So I don't think there should be any problem right?

Re: Using infile but all the dates are missing

FreelanceReinh — Thu, 12 May 2016 10:56:30 GMT

The below data step works:

data test;
infile cards dlm='|' missover dsd;
informat PostDate ANYDTDTE.
         OpenDate ANYDTDTM32. ;

format PostDate DATE9.
       OpenDate DATETIME16. ;

input PostDate OpenDate;
cards;
2015-10-31 00:00|2015-10-31 00:00
;

Therefore, I suspect there is an issue with your data. Could you please post two or three lines of your file (with confidential information overtyped with 'xxxxx', if necessary)?

Re: Using infile but all the dates are missing

FreelanceReinh — Thu, 12 May 2016 11:01:15 GMT

You wrote that "there are no error messages in the Log," but there could be notes indicating data issues. So, showing the relevant part of your log might help, too.

Re: Using infile but all the dates are missing

LaiQ — Thu, 12 May 2016 11:09:18 GMT

Hmm, I tried using only 2 lines of the data:

PostDate|CardID|CardType|OpenDate
2015-09-30 00:00|1311364|0|2015-01-04 00:00
2015-09-30 00:00|959623|0|2010-05-06 00:00

And using the codes:

data want;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'FILE PATH' delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat PostDate ANYDTDTE. ;
informat CardID best32. ;
informat CardType best32. ;
informat OpenDate ANYDTDTM32. ;

format PostDate DATE9. ;
format CardID best12. ;
format CardType best12. ;
format OpenDate DATETIME16. ;

input
PostDate
CardID
CardType
OpenDate
;  
if _ERROR_ then call symput('_EFIERR_',1);  /* set ERROR detection macro variable */
run;

The message in the log:

NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
3316:40
NOTE: The infile 'FILE PATH' is:
File Name=FILE PATH,
RECFM=V,LRECL=32767

NOTE: 2 records were read from the infile 'FILE PATH'.
The minimum record length was 42.
The maximum record length was 43.
NOTE: The data set WANT has 2 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

Re: Using infile but all the dates are missing

LaiQ — Thu, 12 May 2016 11:19:59 GMT

Hey @FreelanceReinh, I managed to get the output if I use:

informat PostDate YMDDTTM32.;

But the format PostDate Date16. will not work. But I still managed to read the date in...

I guess perhaps the informat is wrong in the first place...

Sorry to waste your time!

Re: Using infile but all the dates are missing

Astounding — Thu, 12 May 2016 11:35:51 GMT

POSTDATE takes on datetime values, not date values. Using a date format won't work. If you want to convert POSTDATE to a date value, use:

postdate = datepart(postdate);

Then you can apply date formats to it.

Re: Using infile but all the dates are missing

FreelanceReinh — Thu, 12 May 2016 11:41:06 GMT

I've run your code (with the data you provided) successfully without any change.

Please note that the YMDDTTMw.d informat reads datetime values. That is, you don't get SAS date values (which is what you said you wanted), but SAS datetime values.

Format Date16. doesn't work, because the width w of the DATEw. format is limited to the range 5 - 11.

Also, if you read PostDate with YMDDTTM32., you cannot assign a date format to variable PostDate, but you have to use a format that works for datetime values. Format DTDATE9., for example, displays the date part of a datetime value in the form ddmmmyyyy. This could be suitable for your purposes.

Re: Using infile but all the dates are missing

ballardw — Thu, 12 May 2016 14:11:51 GMT

@LaiQ wrote:

Hey @FreelanceReinh, I managed to get the output if I use:

informat PostDate YMDDTTM32.;

But the format PostDate Date16. will not work. But I still managed to read the date in...

I guess perhaps the informat is wrong in the first place...

Sorry to waste your time!

You could also try YYMMDD10. on reading which would likely just ignore the time portion.