DATA Step, Macro, Functions and more

Splitting date variable into multiple column - Date and Hour

Reply
Contributor
Posts: 56

Splitting date variable into multiple column - Date and Hour

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;

 

 

DATETIMEFORECASTRECORDER
01Oct2017 0:00:001.786429151NJAMS
01Oct2017 1:00:001.594529522NJAMS
01Oct2017 2:00:001.532611857NJAMS
01Oct2017 3:00:001.408968173NJAMS
01Oct2017 4:00:001.452870258NJAMS
01Oct2017 5:00:001.48028587NJAMS
01Oct2017 6:00:001.429192766NJAMS
01Oct2017 7:00:001.466590829NJAMS
01Oct2017 8:00:001.653212565NJAMS
01Oct2017 9:00:001.629932182NJAMS
01Oct2017 10:00:001.845873452NJAMS
01Oct2017 11:00:001.995616508NJAMS
01Oct2017 12:00:002.215903722NJAMS
01Oct2017 13:00:002.189244802NJAMS
01Oct2017 14:00:002.326021186NJAMS
01Oct2017 15:00:002.434289859NJAMS
01Oct2017 16:00:002.346286646NJAMS
01Oct2017 17:00:002.498229065NJAMS
01Oct2017 18:00:002.163318795NJAMS
01Oct2017 19:00:002.117043808NJAMS
01Oct2017 20:00:002.285380221NJAMS
01Oct2017 21:00:002.319593889NJAMS
01Oct2017 22:00:002.271841793NJAMS
01Oct2017 23:00:002.033073233NJAMS
PROC Star
Posts: 1,209

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

Posted in reply to tobyfarms

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;
Contributor
Posts: 56

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

Thank you @draycut  - 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.

Super User
Posts: 13,298

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

Posted in reply to tobyfarms

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.

 

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

Super User
Posts: 13,298

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

Posted in reply to tobyfarms

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

Ask a Question
Discussion stats
  • 4 replies
  • 166 views
  • 0 likes
  • 3 in conversation