Hello
I have a data set that contain 2 fields: date (sas date) and time (numeric time).
I converted the numeric time into sas time and then created a new field of date+time (in date time format).
Now is coming my question:
I want to create a new date field called "calc_new_date" that will be a sas data and will get following value:
IF the time greater equal 24:01 and lower equal 18:29 then calc_new_date will be equal to date.
Else IF time greater equal 18:30 and lower equal 23:59 then calc_new_date will be equal to date+1.
(In other words, if time is later than 18:30 then calc_new_date will be one day after date)
What is the way to do it please?
Here is my tempt to do it
Data have;
format date date9.;
Input date : date9. time;
cards;
'19OCT2022'd 90200
'19OCT2022'd 0
'19OCT2022'd 0
'19OCT2022'd 0
'19OCT2022'd 0
'19OCT2022'd 181400
'19OCT2022'd 160300
'19OCT2022'd 0
'19OCT2022'd 0
'19OCT2022'd 113400
'19OCT2022'd 0
'19OCT2022'd 85700
'19OCT2022'd 0
'19OCT2022'd 0
'20OCT2022'd 93800
'20OCT2022'd 93700
'20OCT2022'd 114600
'20OCT2022'd 0
'21OCT2022'd 0
'21OCT2022'd 85900
'21OCT2022'd 0
'21OCT2022'd 94100
;
Run;
data have2;
set have;
time_=put(time,z6.);
time_new=input(time_,hhmmss6.);
date_time=dhms(date, 0, 0,time_new);
Format time_new hhmm. date_time datetime20. date date9.;
drop time_;
Run;
data want;
set have2;
IF time>='24:01:00't and time<='18:29:00't then calc_new_Date=date;
else IF time>='18:30:00't and time<'23:59:00't then calc_new_Date=date+1;
format calc_new_Date date9.;
Run;
Does "time greater equal 24:01" refer to 24 minutes after the hour? In a date time value you can't have time greater than hour 24 and is a poor idea with clock times as you should never have anything greater than 24:00:00
Your time comparison codes actually leave out a fair amount of time.
Since your example data does not include any times after 18:30 how are we supposed to test that part??
See if this is correct for some added times after 18:30:00
Data have; format date date9.; Input date : date9. time; cards; '19OCT2022'd 90200 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 201500 '19OCT2022'd 0 '19OCT2022'd 181400 '19OCT2022'd 160300 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 113400 '19OCT2022'd 0 '19OCT2022'd 85700 '19OCT2022'd 0 '19OCT2022'd 0 '20OCT2022'd 93800 '20OCT2022'd 93700 '20OCT2022'd 114600 '20OCT2022'd 212000 '21OCT2022'd 0 '21OCT2022'd 85900 '21OCT2022'd 0 '21OCT2022'd 94100 ; Run; data have2; set have; time_=put(time,z6.); time_new=input(time_,hhmmss6.); date_time=dhms(date, 0, 0,time_new); Format time_new hhmm. date_time datetime20. date date9.; drop time_; Run; data want; set have2;
/* edited to use correct time variableIF '00:00:00't<= time <'18:30:00't then calc_new_Date=date;
*/
IF '00:00:00't<= time_new <'18:30:00't then calc_new_Date=date; else calc_new_Date=date+1; format calc_new_Date date9.; Run;
BTW, your data step does not need the quotes and D in the datalines
@Ronein wrote:
Hello
I have a data set that contain 2 fields: date (sas date) and time (numeric time).
I converted the numeric time into sas time and then created a new field of date+time (in date time format).
Now is coming my question:
I want to create a new date field called "calc_new_date" that will be a sas data and will get following value:
IF the time greater equal 24:01 and lower equal 18:29 then calc_new_date will be equal to date.
Else IF time greater equal 18:30 and lower equal 23:59 then calc_new_date will be equal to date+1.
(In other words, if time is later than 18:30 then calc_new_date will be one day after date)
What is the way to do it please?
Here is my tempt to do it
Data have; format date date9.; Input date : date9. time; cards; '19OCT2022'd 90200 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 181400 '19OCT2022'd 160300 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 113400 '19OCT2022'd 0 '19OCT2022'd 85700 '19OCT2022'd 0 '19OCT2022'd 0 '20OCT2022'd 93800 '20OCT2022'd 93700 '20OCT2022'd 114600 '20OCT2022'd 0 '21OCT2022'd 0 '21OCT2022'd 85900 '21OCT2022'd 0 '21OCT2022'd 94100 ; Run; data have2; set have; time_=put(time,z6.); time_new=input(time_,hhmmss6.); date_time=dhms(date, 0, 0,time_new); Format time_new hhmm. date_time datetime20. date date9.; drop time_; Run; data want; set have2; IF time>='24:01:00't and time<='18:29:00't then calc_new_Date=date; else IF time>='18:30:00't and time<'23:59:00't then calc_new_Date=date+1; format calc_new_Date date9.; Run;
Does "time greater equal 24:01" refer to 24 minutes after the hour? In a date time value you can't have time greater than hour 24 and is a poor idea with clock times as you should never have anything greater than 24:00:00
Your time comparison codes actually leave out a fair amount of time.
Since your example data does not include any times after 18:30 how are we supposed to test that part??
See if this is correct for some added times after 18:30:00
Data have; format date date9.; Input date : date9. time; cards; '19OCT2022'd 90200 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 201500 '19OCT2022'd 0 '19OCT2022'd 181400 '19OCT2022'd 160300 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 113400 '19OCT2022'd 0 '19OCT2022'd 85700 '19OCT2022'd 0 '19OCT2022'd 0 '20OCT2022'd 93800 '20OCT2022'd 93700 '20OCT2022'd 114600 '20OCT2022'd 212000 '21OCT2022'd 0 '21OCT2022'd 85900 '21OCT2022'd 0 '21OCT2022'd 94100 ; Run; data have2; set have; time_=put(time,z6.); time_new=input(time_,hhmmss6.); date_time=dhms(date, 0, 0,time_new); Format time_new hhmm. date_time datetime20. date date9.; drop time_; Run; data want; set have2;
/* edited to use correct time variableIF '00:00:00't<= time <'18:30:00't then calc_new_Date=date;
*/
IF '00:00:00't<= time_new <'18:30:00't then calc_new_Date=date; else calc_new_Date=date+1; format calc_new_Date date9.; Run;
BTW, your data step does not need the quotes and D in the datalines
@Ronein wrote:
Hello
I have a data set that contain 2 fields: date (sas date) and time (numeric time).
I converted the numeric time into sas time and then created a new field of date+time (in date time format).
Now is coming my question:
I want to create a new date field called "calc_new_date" that will be a sas data and will get following value:
IF the time greater equal 24:01 and lower equal 18:29 then calc_new_date will be equal to date.
Else IF time greater equal 18:30 and lower equal 23:59 then calc_new_date will be equal to date+1.
(In other words, if time is later than 18:30 then calc_new_date will be one day after date)
What is the way to do it please?
Here is my tempt to do it
Data have; format date date9.; Input date : date9. time; cards; '19OCT2022'd 90200 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 181400 '19OCT2022'd 160300 '19OCT2022'd 0 '19OCT2022'd 0 '19OCT2022'd 113400 '19OCT2022'd 0 '19OCT2022'd 85700 '19OCT2022'd 0 '19OCT2022'd 0 '20OCT2022'd 93800 '20OCT2022'd 93700 '20OCT2022'd 114600 '20OCT2022'd 0 '21OCT2022'd 0 '21OCT2022'd 85900 '21OCT2022'd 0 '21OCT2022'd 94100 ; Run; data have2; set have; time_=put(time,z6.); time_new=input(time_,hhmmss6.); date_time=dhms(date, 0, 0,time_new); Format time_new hhmm. date_time datetime20. date date9.; drop time_; Run; data want; set have2; IF time>='24:01:00't and time<='18:29:00't then calc_new_Date=date; else IF time>='18:30:00't and time<'23:59:00't then calc_new_Date=date+1; format calc_new_Date date9.; Run;
So @ballardw code runs, but it should still be time_new not time.
Time is numeric so SAS will convert it to a time but it's not doing the correct calculations.
You need to change the variable name.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.