topic Re: Expression to convert Date9. to Datetime? in New SAS User

Expression to convert Date9. to Datetime?

Soulbroda — Fri, 17 May 2019 21:46:11 GMT

I have a source file with it's date in the format below;

When loading it into my SAS table, I used the expression: "COMPRESS(BusinessDate, '-')" to remove the hyphen and got the date in the load table like below;

In my Work job, I use the expression: "input(VALID_FROM_DTTM,yymmdd8.)" to convert the numeric values to a Date value to look like below;

MY QUESTION

I am trying to use this in a stage job that only accepts the date value in a datetime format. I have asked some guys in the office and I get the same response that I can't convert date to datetime. I have had some problems resolved here and was just thinking I should give it a shot. Is there a way I can get my VALID_FROM_DTTM in Datetime value?

Re: Expression to convert Date9. to Datetime?

VDD — Fri, 17 May 2019 21:51:48 GMT

if your date doesn't have a time attached to it as a datetime does it will not work as expected.

you will always get all zero's for the time-part that you add to the date.

remember the addition of nothing is nothing.

Re: Expression to convert Date9. to Datetime?

Soulbroda — Fri, 17 May 2019 21:57:27 GMT

Thanks for your quick response.

I actually found this expression on here: dhms (VALID_FROM_DTTM,0,0,0) and it was able to express my column (VALID_FROM_DTTM) as 06JUN2018:00:00:00.000 but returns an error WARNING: Apparent symbolic reference BUSINESSDATE_DTTM not resolved.

What does this error mean and how can I resolve it? I actually don't mind having my datetime in the format above with all zeros.

Re: Expression to convert Date9. to Datetime?

Tom — Fri, 17 May 2019 22:06:14 GMT

If COMPRESS() worked on the variable then it is neither a DATE nor a DATETIME value. Date values are stored as a number of days and Datetime values are stored as a number of seconds. So they are both NUMERIC variables. If the value has hyphens in it then it is a CHARACTER variable.

You can use the INPUT() function to convert text into values.

The DHMS() (Days Hours Minutes Seconds) function can convert from date to datetime. But you need to pick as time of day during that day that you want. Typically the timepart is set to zero.

For both DATE and DATETIME values make sure to attach an appropriate format that will allow SAS to display the number in a way that humans will understand.

Let's run a little test to see how to do that.

data _null_;
  BusinessDate = '2018-06-06';
  ActualDate = input(BusinessDate,yymmdd10.);
  ActualDateTime = dhms(ActualDate,0,0,0);
  format ActualDate DATE9.  ActualDateTime DATETIME20.;
  put (_all_) (=);
run;

BusinessDate=2018-06-06 ActualDate=06JUN2018 ActualDateTime=06JUN2018:00:00:00

Re: Expression to convert Date9. to Datetime?

Tom — Fri, 17 May 2019 22:09:13 GMT

@Soulbroda wrote:

Thanks for your quick response.

I actually found this expression on here: dhms (VALID_FROM_DTTM,0,0,0) and it was able to express my column (VALID_FROM_DTTM) as 06JUN2018:00:00:00.000 but returns an error WARNING: Apparent symbolic reference BUSINESSDATE_DTTM not resolved.

What does this error mean and how can I resolve it? I actually don't mind having my datetime in the format above with all zeros.

That error means that your code included the text &BUSINESSDATE_DTTM. The ampersand is the trigger to the macro processor that you want to reference the value of a macro variable. Your screen shots make it look like you values are in actual datasets, not in macro variables.

Re: Expression to convert Date9. to Datetime?

VDD — Fri, 17 May 2019 22:37:56 GMT

Looks like another case where SQL Server programmers are using the incorrect data type to define the date.

why carry extra weight in a column to store a datetime when a numerical value store the correct input? This leads to confusion for the meaning of a datetime field.

I gave meds to the client on 25JAN2017:00:00:00.000 because the doctor placed the order at 25JAN2017:00:00:00.000

how do you measure if the meds were give 24 minutes before the doctor placed the order and the patient died at 25JAN2017:00:25:00.000

stupid to store a bunch of 0's when there is no meaning to them.