topic Re: Splitting date variable into multiple column - Date and Hour in SAS Programming

Splitting date variable into multiple column - Date and Hour

tobyfarms — Fri, 08 Dec 2017 14:37:00 GMT

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

Re: Splitting date variable into multiple column - Date and Hour

PeterClemmensen — Fri, 08 Dec 2017 14:48:26 GMT

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;

Re: Splitting date variable into multiple column - Date and Hour

ballardw — Fri, 08 Dec 2017 15:43:14 GMT

Example data: Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 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.

Re: Splitting date variable into multiple column - Date and Hour

tobyfarms — Fri, 08 Dec 2017 18:18:13 GMT

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.

Re: Splitting date variable into multiple column - Date and Hour

ballardw — Fri, 08 Dec 2017 21:21:09 GMT

@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.