BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

I have datetime variable.  For example:  start_date = '07JUN2014:00:54:00"  

I want to subtract 3 hours from the above date in  PROC SQL.

So output will be 06JUN2014:09:54:00

Can anybody help me in this?

9 REPLIES 9
PaigeMiller
Diamond | Level 26

Use the INTNX function

--
Paige Miller
ballardw
Super User

The function INTNX is used to change date and time values

intnx('hour', start_date,-3) should do it. You don't say if you want to create a new variable or overwrite the existing.

PGStats
Opal | Level 21

If you want to keep the same minutes and seconds, you must specify "SAME" as the alignment argument to INTNX:

data _null_;

start_date = '07JUN2014:00:54:00'dt;

new_date1 = intnx("HOUR",start_date,-3);

new_date2 = intnx("HOUR",start_date,-3,"SAME");

put (start_date new_date1 new_date2) (:datetime21. /);

run;

07JUN2014:00:54:00

06JUN2014:21:00:00

06JUN2014:21:54:00

PG

PG
pp2014
Fluorite | Level 6

Thanks.

If I have date in the following format (data is in Oracle database) 

start_date = '6/7/2014 12:54:00 AM'

How do I subtract 3 hours to get '6/6/2014  9:54:00 PM'?

PGStats
Opal | Level 21

I don't do Oracle.

PG
PGStats
Opal | Level 21

... But, if you bring in your date as a string, you could do as follows:

data _null_;

dtxt =  '6/7/2014 12:54:00 AM';

start_date = input(dtxt, anydtdtm.);

new_date = intnx("HOUR",start_date,-3,"SAME");

put new_date :dateAMPM20.;

run;

06JUN14:09:54:00 PM

Or in terms of SQL

select intnx("HOUR", input(OracleTextDate, anydtdtm.), -3, "SAME") as new_date, ...

PG

PG
pp2014
Fluorite | Level 6

Thanks for help...

Tom
Super User Tom
Super User

DATETIME and TIME values are both stored as number of seconds.

START_DATE - '03:00't

Ksharp
Super User

START_DATE - 3*60*60

sas-innovate-white.png

Missed SAS Innovate in Orlando?

Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.

 

Register now

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 25587 views
  • 7 likes
  • 6 in conversation