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!
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?
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)?
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.
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
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!
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.
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.
@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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: