BookmarkSubscribeRSS Feed
tobyfarms
Fluorite | Level 6

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
4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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;
tobyfarms
Fluorite | Level 6

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.

ballardw
Super User

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.

ballardw
Super User

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

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
  • 4 replies
  • 4704 views
  • 0 likes
  • 3 in conversation