Help using Base SAS procedures

How to subtract hours from datetime Variable in proc sql

Reply
Frequent Contributor
Posts: 127

How to subtract hours from datetime Variable in proc sql

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?

Trusted Advisor
Posts: 1,918

Re: How to subtract hours from datetime Variable in proc sql

Use the INTNX function

Super User
Posts: 11,343

Re: How to subtract hours from datetime Variable in proc sql

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.

Respected Advisor
Posts: 4,923

Re: How to subtract hours from datetime Variable in proc sql

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
Frequent Contributor
Posts: 127

Re: How to subtract hours from datetime Variable in proc sql

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'?

Respected Advisor
Posts: 4,923

Re: How to subtract hours from datetime Variable in proc sql

I don't do Oracle.

PG
Respected Advisor
Posts: 4,923

Re: How to subtract hours from datetime Variable in proc sql

... 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
Frequent Contributor
Posts: 127

Re: How to subtract hours from datetime Variable in proc sql

Thanks for help...

Super User
Super User
Posts: 7,050

Re: How to subtract hours from datetime Variable in proc sql

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

START_DATE - '03:00't

Super User
Posts: 10,028

Re: How to subtract hours from datetime Variable in proc sql

START_DATE - 3*60*60

Ask a Question
Discussion stats
  • 9 replies
  • 6474 views
  • 3 likes
  • 6 in conversation