BookmarkSubscribeRSS Feed
LaiQ
Calcite | Level 5

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!

9 REPLIES 9
FreelanceReinh
Jade | Level 19

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?

LaiQ
Calcite | Level 5
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?
FreelanceReinh
Jade | Level 19

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

FreelanceReinh
Jade | Level 19

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.

LaiQ
Calcite | Level 5

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

LaiQ
Calcite | Level 5

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!

Astounding
PROC Star

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.

FreelanceReinh
Jade | Level 19

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.

 

ballardw
Super User

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 3779 views
  • 0 likes
  • 4 in conversation