topic Re: Converting UTC date/time observations to usable format in SAS Programming

Converting UTC date/time observations to usable format

starky987 — Thu, 19 Jan 2017 17:37:19 GMT

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

Re: Converting UTC date/time observations to usable format

art297 — Thu, 19 Jan 2017 18:05:07 GMT

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

Re: Converting UTC date/time observations to usable format

starky987 — Thu, 19 Jan 2017 18:07:58 GMT

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):(Column).
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):(Column).
8484014 at 321:21

ERROR: Informat E8601DZ16. is incorrect for variable TIMESTAMP_UTC.

Thanks!

Re: Converting UTC date/time observations to usable format

starky987 — Thu, 19 Jan 2017 18:11:35 GMT

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?

Re: Converting UTC date/time observations to usable format

art297 — Thu, 19 Jan 2017 18:17:18 GMT

My Bad! Try:

newvar =year( datepart(date_var));

Art, CEO, AnalystFinder.com

Re: Converting UTC date/time observations to usable format

starky987 — Thu, 19 Jan 2017 18:21:55 GMT

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

Re: Converting UTC date/time observations to usable format

art297 — Thu, 19 Jan 2017 18:24:51 GMT

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

Re: Converting UTC date/time observations to usable format

starky987 — Thu, 19 Jan 2017 18:30:14 GMT

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!

Re: Converting UTC date/time observations to usable format

art297 — Thu, 19 Jan 2017 18:43:12 GMT

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

Re: Converting UTC date/time observations to usable format

starky987 — Thu, 19 Jan 2017 19:25:14 GMT

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.

Re: Converting UTC date/time observations to usable format

art297 — Thu, 19 Jan 2017 19:54:35 GMT

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/td-p/128851

Art, CEO, AnalystFinder.com