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

How to subtract hours from datetime Variable in proc sql

pp2014 — Fri, 12 Sep 2014 14:34:29 GMT

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?

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

PaigeMiller — Fri, 12 Sep 2014 14:40:43 GMT

Use the INTNX function

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

ballardw — Fri, 12 Sep 2014 14:41:59 GMT

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.

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

Tom — Fri, 12 Sep 2014 14:57:55 GMT

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

START_DATE - '03:00't

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

PGStats — Fri, 12 Sep 2014 15:24:16 GMT

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

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

pp2014 — Fri, 12 Sep 2014 15:32:34 GMT

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

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

PGStats — Fri, 12 Sep 2014 15:43:02 GMT

I don't do Oracle.

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

PGStats — Fri, 12 Sep 2014 15:59:14 GMT

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

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

pp2014 — Fri, 12 Sep 2014 16:36:33 GMT

Thanks for help...

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

Ksharp — Sat, 13 Sep 2014 14:55:52 GMT

START_DATE - 3*60*60