Converting UTC date/time observations to usable format

Reply
Occasional Contributor
Posts: 16

Converting UTC date/time observations to usable format

Accepted Solution 

I have a file that is in CSV format. The time stamps are in the format which I have now successfully imported into sas (thanks to a member named Art).

 

Once in SAS the formatting looks like

 

08APR11:04:00:00

16APR11:04:00:00

 

These corresond to April 8, 2011 @ 4:00 UTC and April 16, 2011 @ 4:00 UTC.

 

What I am hoping to do is to separate each portion of the date out into its own variable such as Year, Month, Day, Hour, Minute.

 

I have typically done this in the past when my data is formatted as YYMMDDN8. by simply using newvar = year(date_var) or newvar = month(date_var), etc...

 

This method does not seem to work with my dates formatted as DATETIME16. and informat E8601DZ16. However, This formatting is required to import the original CSV file where the date are formatted 

 

2011-12-30T05:00:00Z

2012-04-09T04:00:00Z

2014-03-10T04:00:00Z

 

The import code I use (which was provided by Art) is below

 

data WORK.ABC ;
  infile 'E:\Research\Data\filename.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
  informat SOURCE $10. ;
  informat SYMBOL $4. ;
  informat TIMESTAMP_UTC e8601dz. ;
  informat BULLISH_INTENSITY best32. ;
  informat BEARISH_INTENSITY best32. ;
  informat BULL_MINUS_BEAR best32. ;
  informat BULL_SCORED_MESSAGES best32. ;
  informat BEAR_SCORED_MESSAGES best32. ;
  informat BULL_BEAR_MSG_RATIO best32. ;
  informat TOTAL_SCANNED_MESSAGES best32. ;
  format SOURCE $10. ;
  format SYMBOL $4. ;
  format TIMESTAMP_UTC datetime. ;
  format BULLISH_INTENSITY best12. ;
  format BEARISH_INTENSITY best12. ;
  format BULL_MINUS_BEAR best12. ;
  format BULL_SCORED_MESSAGES best12. ;
  format BEAR_SCORED_MESSAGES best12. ;
  format BULL_BEAR_MSG_RATIO best12. ;
  format TOTAL_SCANNED_MESSAGES best12. ;
  input
   SOURCE $
   SYMBOL $
   TIMESTAMP_UTC
   BULLISH_INTENSITY
   BEARISH_INTENSITY
   BULL_MINUS_BEAR
   BULL_SCORED_MESSAGES
   BEAR_SCORED_MESSAGES
   BULL_BEAR_MSG_RATIO
   TOTAL_SCANNED_MESSAGES
  ;
run;

 

I am at a loss here so anyhelp would be greatly appreciated!

 

Thanks

Esteemed Advisor
Posts: 7,293

Re: Converting UTC date/time observations to usable format

I think you only have to embed the month, day, year, or hour, minute, second functions with either the DATEPART() or TIMEPART functions. e.g.

 

newvar = datepart(year(date_var));

HTH,

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 16

Re: Converting UTC date/time observations to usable format

Art,

 

I get the following error when doing this...

 

NOTE: Invalid argument to function YEAR(1617854400) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=08APR11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=2 year=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function YEAR(1618545600) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=16APR11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=3 year=. _ERROR_=1 _N_=2
NOTE: Invalid argument to function YEAR(1618632000) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=17APR11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=2 year=. _ERROR_=1 _N_=3
NOTE: Invalid argument to function YEAR(1618891200) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=20APR11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=1 year=. _ERROR_=1 _N_=4
NOTE: Invalid argument to function YEAR(1626667200) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=19JUL11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=3 year=. _ERROR_=1 _N_=5
NOTE: Invalid argument to function YEAR(1626753600) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=20JUL11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=4 year=. _ERROR_=1 _N_=6
NOTE: Invalid argument to function YEAR(1626840000) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=21JUL11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=1 year=. _ERROR_=1 _N_=7
NOTE: Invalid argument to function YEAR(1627012800) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=23JUL11:04:00:00 BULLISH_INTENSITY=2
BEARISH_INTENSITY=2.83 BULL_MINUS_BEAR=-0.83 BULL_SCORED_MESSAGES=1 BEAR_SCORED_MESSAGES=1
BULL_BEAR_MSG_RATIO=1 TOTAL_SCANNED_MESSAGES=2 year=. _ERROR_=1 _N_=8
NOTE: Invalid argument to function YEAR(1627099200) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=24JUL11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=4 year=. _ERROR_=1 _N_=9
NOTE: Invalid argument to function YEAR(1627185600) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=25JUL11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=5 year=. _ERROR_=1 _N_=10
NOTE: Invalid argument to function YEAR(1627272000) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=26JUL11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=1 year=. _ERROR_=1 _N_=11
NOTE: Invalid argument to function YEAR(1627444800) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=28JUL11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=4 year=. _ERROR_=1 _N_=12
NOTE: Invalid argument to function YEAR(1627531200) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=29JUL11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=2 year=. _ERROR_=1 _N_=13
NOTE: Invalid argument to function YEAR(1627617600) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=30JUL11:04:00:00 BULLISH_INTENSITY=1.63
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=1.63 BULL_SCORED_MESSAGES=1 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=6 year=. _ERROR_=1 _N_=14
NOTE: Invalid argument to function YEAR(1627790400) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=01AUG11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=2 year=. _ERROR_=1 _N_=15
NOTE: Invalid argument to function YEAR(1627876800) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=02AUG11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=2 year=. _ERROR_=1 _N_=16
NOTE: Invalid argument to function YEAR(1628136000) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=05AUG11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=2 year=. _ERROR_=1 _N_=17
NOTE: Invalid argument to function YEAR(1628222400) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=06AUG11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=0 BULL_MINUS_BEAR=0 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=0
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=4 year=. _ERROR_=1 _N_=18
NOTE: Invalid argument to function YEAR(1636171200) at line 321 column 21.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=06NOV11:04:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=2.83 BULL_MINUS_BEAR=-2.83 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=1
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=1 year=. _ERROR_=1 _N_=19
NOTE: Invalid argument to function YEAR(1636347600) at line 321 column 21.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
SOURCE=twitter_enha SYMBOL=10GEN TIMESTAMP_UTC=08NOV11:05:00:00 BULLISH_INTENSITY=0
BEARISH_INTENSITY=2.83 BULL_MINUS_BEAR=-2.83 BULL_SCORED_MESSAGES=0 BEAR_SCORED_MESSAGES=1
BULL_BEAR_MSG_RATIO=0 TOTAL_SCANNED_MESSAGES=1 year=. _ERROR_=1 _N_=20
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line)Smiley SadColumn).
8484014 at 321:12
NOTE: Mathematical operations could not be performed at the following places. The results of
the operations have been set to missing values.
Each place is given by: (Number of times) at (Line)Smiley SadColumn).
8484014 at 321:21


ERROR: Informat E8601DZ16. is incorrect for variable TIMESTAMP_UTC.

 

Thanks!

 

 

Occasional Contributor
Posts: 16

Re: Converting UTC date/time observations to usable format

One additional point, when I run the import code you suggested yesterday, the data imports. When I open the file within SAS after importing it and if I right click on the UTC date/time variable to look at its properties, it is listed as informat E8601DZ16, but as soon as I close the file I get 

 

ERROR: Informat E8601DZ16. is incorrect for variable TIMESTAMP_UTC.

 

Could this be causing an issue?

 

Esteemed Advisor
Posts: 7,293

Re: Converting UTC date/time observations to usable format

My Bad! Try:

newvar =year( datepart(date_var));

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 16

Re: Converting UTC date/time observations to usable format

Almost there haha, sorry about all these questions.

 

Using the following code:

 

data b; set a;
year=year(datepart(timestamp_utc));
month=month(datepart(timestamp_utc));
day=day(datepart(timestamp_utc));
hour=hour(datepart(timestamp_utc));
minute=minute(datepart(timestamp_utc));
run;

 

For the date/time variable 08APR11:04:00:00 I get the following

year = 2011 (correct)

month = 4 (correct)

day = 8 (correct)

hour = 5 (incorrect)

minute = 12 (incorrect)

 

Thanks

 

Esteemed Advisor
Posts: 7,293

Re: Converting UTC date/time observations to usable format

You missed something from my first post in this thread. Try the following:

 

year=year(datepart(timestamp_utc));
month=month(datepart(timestamp_utc));
day=day(datepart(timestamp_utc));
hour=hour(timepart(timestamp_utc));
minute=minute(timepart(timestamp_utc));
run;

HTH,

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 16

Re: Converting UTC date/time observations to usable format

Alright, its working now. I just have one final question. My date is currently listed in UTC time. Is there a way to convert it to EST? I know that I can do it manually, but UTC does not account for daylight savings so it would be someone difficult to manually adjust for daylight savings each year. Does SAS have a way of accomplishing this?

 

Thanks for all the help!

Esteemed Advisor
Posts: 7,293

Re: Converting UTC date/time observations to usable format

I think you may want to try importing them with a different informat then, namely E8601LZ16

 

See: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003065425.htm

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 16

Re: Converting UTC date/time observations to usable format

With the new informat, I get the error "invalid data for timestamp_utc." This occurs for all observations and occurs regardless of whether or not I use E8601LZ16 or E8601LZ.

Esteemed Advisor
Posts: 7,293

Re: Converting UTC date/time observations to usable format

My misunderstanding of your data. Use the informat I originally suggested, but then subtract the number of secons from the value.

 

SAS datetimes represent the number of seconds since January 1, 1960.

 

Take a look at: https://communities.sas.com/t5/Base-SAS-Programming/Is-there-a-way-to-convert-time-from-EST-to-GMT/t...

 

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 10 replies
  • 166 views
  • 0 likes
  • 2 in conversation