I know this is simple, but other posts are not working for me. Maybe it is the formatting.
I need to split the 1st column (Datetime) into 2 columns. (date) and (hour). Date column should remain in the same format and positions 9 and 10 are the hour.
data work.ace_profiles_test;
set work.'new ace_profiles_test'n;
Date=substr(datetime,1,8);
hour=substr(datetime,9,2);
date=input(datetime, date9.) ; /*format date date9.; */
run;
DATETIME | FORECAST | RECORDER |
01Oct2017 0:00:00 | 1.786429151 | NJAMS |
01Oct2017 1:00:00 | 1.594529522 | NJAMS |
01Oct2017 2:00:00 | 1.532611857 | NJAMS |
01Oct2017 3:00:00 | 1.408968173 | NJAMS |
01Oct2017 4:00:00 | 1.452870258 | NJAMS |
01Oct2017 5:00:00 | 1.48028587 | NJAMS |
01Oct2017 6:00:00 | 1.429192766 | NJAMS |
01Oct2017 7:00:00 | 1.466590829 | NJAMS |
01Oct2017 8:00:00 | 1.653212565 | NJAMS |
01Oct2017 9:00:00 | 1.629932182 | NJAMS |
01Oct2017 10:00:00 | 1.845873452 | NJAMS |
01Oct2017 11:00:00 | 1.995616508 | NJAMS |
01Oct2017 12:00:00 | 2.215903722 | NJAMS |
01Oct2017 13:00:00 | 2.189244802 | NJAMS |
01Oct2017 14:00:00 | 2.326021186 | NJAMS |
01Oct2017 15:00:00 | 2.434289859 | NJAMS |
01Oct2017 16:00:00 | 2.346286646 | NJAMS |
01Oct2017 17:00:00 | 2.498229065 | NJAMS |
01Oct2017 18:00:00 | 2.163318795 | NJAMS |
01Oct2017 19:00:00 | 2.117043808 | NJAMS |
01Oct2017 20:00:00 | 2.285380221 | NJAMS |
01Oct2017 21:00:00 | 2.319593889 | NJAMS |
01Oct2017 22:00:00 | 2.271841793 | NJAMS |
01Oct2017 23:00:00 | 2.033073233 | NJAMS |
Always a good idea to convert dates or datetime values stored as text to valid SAS dates/datetime values first, then take advantage of SAS date functions afterwards
data have;
input DATETIME:$50. FORECAST:best20. RECORDER$;
infile datalines4 dlm=',';
datalines;
01Oct2017 0:00:00,1.786429151,NJAMS
01Oct2017 1:00:00,1.594529522,NJAMS
01Oct2017 2:00:00,1.532611857,NJAMS
01Oct2017 3:00:00,1.408968173,NJAMS
01Oct2017 4:00:00,1.452870258,NJAMS
01Oct2017 5:00:00,1.48028587,NJAMS
01Oct2017 6:00:00,1.429192766,NJAMS
01Oct2017 7:00:00,1.466590829,NJAMS
01Oct2017 8:00:00,1.653212565,NJAMS
01Oct2017 9:00:00,1.629932182,NJAMS
01Oct2017 10:00:00,1.845873452,NJAMS
01Oct2017 11:00:00,1.995616508,NJAMS
01Oct2017 12:00:00,2.215903722,NJAMS
01Oct2017 13:00:00,2.189244802,NJAMS
01Oct2017 14:00:00,2.326021186,NJAMS
01Oct2017 15:00:00,2.434289859,NJAMS
01Oct2017 16:00:00,2.346286646,NJAMS
01Oct2017 17:00:00,2.498229065,NJAMS
01Oct2017 18:00:00,2.163318795,NJAMS
01Oct2017 19:00:00,2.117043808,NJAMS
01Oct2017 20:00:00,2.285380221,NJAMS
01Oct2017 21:00:00,2.319593889,NJAMS
01Oct2017 22:00:00,2.271841793,NJAMS
01Oct2017 23:00:00,2.033073233,NJAMS
;;;;
data want;
set have;
dt=input(DATETIME, ANYDTDTM18.);
date=datepart(dt);
hour=hour(dt);
format dt datetime18. date date9.;
run;
Thank you @PeterClemmensen - the current format of the datetime column is DATETIME18, which may be causing the issue of my code not converting the field into a date field.
Example data: Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
From your post your datetime (NOT a date) could be character, we have no way to know.
@PeterClemmensen was kind enough to show how example data should be presented. But he had to make choices about reading your example and his variable types may not be yours.
@tobyfarms wrote:
I know this is simple, but other posts are not working for me. Maybe it is the formatting.
I need to split the 1st column (Datetime) into 2 columns. (date) and (hour). Date column should remain in the same format and positions 9 and 10 are the hour.
data work.ace_profiles_test; set work.'new ace_profiles_test'n; Date=substr(datetime,1,8); hour=substr(datetime,9,2); date=input(datetime, date9.) ; /*format date date9.; */ run;
Show the log from when your run that code. You should have a message like this:
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
Your first use of date with the SUBSTR function creates a character variable date. (Also indicating that your datetime is character not numeric as in @PeterClemmensen's example). The second use of date with input 1) does the conversion of the first 9 characters to a SAS date value and 2) stores the resulting number of days into a the previously created character variable.
If you remove the first date= statement then you will have a numeric value from the data=input(...
Then add the statement
Format date date9.;
so that the date numeric value is displayed in the date9. format.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.