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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.