forum_wrds | date format

date format

jc625

Posted: 04 November 2015 05:48 AM

[ Ignore ]

Newbie

Total Posts: 14

Joined 2014-11-08

Dear Joost,

I have a question about the format of date. My raw data is TIMESTAMP_UTC 11/3/2015 12:00:02 AM in csv file.
I use import wizard, and the imported date is 11/3/2015. I pressed F4 and got the code:

data RP.fulldata    ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile 'D:\Desktop\RP\2000-2012.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat TIMESTAMP_UTC mmddyy10. ; 

Then, I changed the format into mdyampm25.2:

data RP.fulldata    ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile 'D:\Desktop\RP\2000-2012.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat TIMESTAMP_UTC mdyampm25.2 ; 

Then all the TIMESTAMP_UTCs are missing. The note in log is that “Invalid data for TIMESTAMP_UTC”.

I tried another method, and the code is as follows.

proc import datafile="D:\\Desktop\\RP\\2000-2012.csv" dbms=csv out=rp.fulldata replace;
getnames=yes;
guessingrows=32767;
run; 

However, the imputed date is like this 03/01/2000. The DESC is that:

data RP.FULLDATA    ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile 'D:\Desktop\RP\2000-2012.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat TIMESTAMP_UTC $13. ; 

Why the length of the raw data is 13?

I am really confused. Could you help me? Thank you.

Regards,
Emily

Profile

Joost Impink

Posted: 04 November 2015 08:56 AM

[ Ignore ] [ # 1 ]

Administrator

Total Posts: 901

Joined 2011-09-19

hi Emily,

Did you try coding the import yourself instead of a wizard? First, read the csv and treat the timestamp as a large string. That should be easy; the harder part is to convert the string into a date/time. You can use string functions like index and substring

index: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212242.htm
substring: http://support.sas.com/documentation/cdl/en/sqlproc/68053/HTML/default/viewer.htm#n03g2uan994purn1lrpwnjg9cnx3.htm
mdy: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000199044.htm

Use the index function to find the position of a space (’ ‘) as this separates the date from the time; then use substring to get the first piece (the date) and the second piece (the time), as well as getting the day, month, year, etc.
Use mdy to turn the strings for day, month, year into a SAS date.

Hope this helps,

Joost

Signature

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile

‹‹ Discretionary Accruals Fama French 49 YES 49 !!! ››