BookmarkSubscribeRSS Feed
starky987
Calcite | Level 5
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

10 REPLIES 10
art297
Opal | Level 21

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

 

starky987
Calcite | Level 5

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!

 

 

starky987
Calcite | Level 5

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?

 

art297
Opal | Level 21

My Bad! Try:

newvar =year( datepart(date_var));

Art, CEO, AnalystFinder.com

 

starky987
Calcite | Level 5

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

 

art297
Opal | Level 21

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

 

starky987
Calcite | Level 5

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!

art297
Opal | Level 21

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

 

starky987
Calcite | Level 5

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.

art297
Opal | Level 21

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

 

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
  • 10 replies
  • 1476 views
  • 0 likes
  • 2 in conversation