Add timestamp to MMDDYY10. column in PROC SQL (SAS E Guide)

Question

I'm having an issue adding time to a date with PROC SQL. Using PROC SQL, the column I am pulling is formatted as a MMDDYY10.. I need to add x days, y hours, and z minutes to this date. My biggest issue now is just getting a zeroed out time stamp attached.

I promise, I've been all over the web before coming here and nothing has been exactly what I need. I feel crazy, this doesn't seem like something that should be so hard to do!

The closest I've been to my goal is using:

SELECT
    DHMS(myDateVar,0,0,0) AS myDateVar FORMAT = DATETIME22.
FROM
    tableName

However, this just returns my date variable (which is just today for now) with the random time of 20:48:37

Ultimately, the data set being selected is going to be appended to a SQL server table that SAS recognizes as a DATETIME22. formatted column.

So, can anyone give me a hand adding x days, y hours, and z minutes to a date MMDDYY10. in a PROC SQL select statement?

I believe you simply need to change your format to be datetime22.. You have the decimal in the wrong place — Robert Penridge
@RobertPenridge Sorry, that was a typo. I'll fix it in an edit. — Joshua Schlichting

Reeza Reeza · Accepted Answer · 2015-09-18T18:09:47

I can't replicate your problem, even with Tom's suggest fix. Most likely you have some issue with your date variable. Is it a SAS date variable? You can add hours, minutes and seconds by using DHMS function and days by using INTNX function.

proc sql;
create table want as
select dhms(date, 0, 0, 0) as mydate format=datetime22.,
        dhms(today(), 0, 0, 0) as mydate2 format=datetime22.
from sashelp.stocks;
quit;

proc print data=want(obs=20);
var mydate:;
run;

EDIT: Your "date" variable has a decimal component when you brought it in from Excel. Not sure why, but you can fix it by using the floor() function to get only the number and the answer you're looking for. I would highly suggest fixing that issue first as you may have imported the date incorrectly or SAS may be interpreting it incorrectly.

data have;
format date date9.;
input date;
cards;
10918.12
10918.23
10918.58
;
run;
proc print data=have;
run;

data want;
set have;
date_new=dhms(date, 0,0,0);
format date_new datetime22.;
date_want=dhms(floor(date), 0, 0,0);
format date_want datetime22.;
run;
proc print; run;

Add timestamp to MMDDYY10. column in PROC SQL (SAS E Guide)

1 Answers