topic Re: Compatibility between date types in SAS Programming

Compatibility between date types

AlexSP — Thu, 30 Sep 2021 14:39:57 GMT

Hi!

I performed a union between two selects.
In the first select the date field (ajt.DTC_MOV)) is numeric in datetime 22.3 format. The results are in the format 03JAN2019:00:00:00.000.

In the second select, the date field (ajt.DTC_MOV) is numeric without a defined format (the dates are extracted from an excel spreadsheet). The results are in the format 1935705600.

When performing the UNION data field it shows the results in the format 1935705600.

My question is how to display them in the format 03JAN2019:00:00:00.000. I've tried the solutions below without success in the select clause:
- DHMS(ajt.DTC_MOV,0,0,0)
- datepart (DHMS(ajt.DTC_MOV,0,0,0)) format = datetime22.3
- datepart (ajt.DTC_MOV) format = datetime22.3

Thanks.

Re: Compatibility between date types

PaigeMiller — Thu, 30 Sep 2021 14:53:20 GMT

The formats in the two tables are irrelevant here. The key issue is whether the variables are date/time variables or date variables.

Since you say one is formatted as datetime22.3 and the other shows as 1935705600, these are most likely both date/time values, and so there should be no problem doing a UNION as these are both date/time values. (Note: sometimes date and datetime values in Excel have to be adjusted in SAS, search the SAS Communities for the proper adjustment)

There is also no reason to use DATEPART if you want the results to look like 03JAN2019:00:00:00.000, because DATEPART gives you dates, not date/times which is what you want. So take DATEPART out of your code, and you should find it works.

Re: Compatibility between date types

AlexSP — Thu, 30 Sep 2021 15:11:41 GMT

Thank you for your prompt response, but the result is still in the 1924214400 format.

Re: Compatibility between date types

ballardw — Thu, 30 Sep 2021 15:17:23 GMT

@AlexSP wrote:

Thank you for your prompt response, but the result is still in the 1924214400 format.

Show your actual code from the LOG with the notes and messages.

If your numeric value of 192421440 shows 22DEC2020:00:00:00 or similar in Excel then your issue is in assigning the format. Or you didn't replace the first data set and are seeing the previous values.

Re: Compatibility between date types

PaigeMiller — Thu, 30 Sep 2021 15:33:27 GMT

We can't go further without seeing the LOG for the PROC SQL (all of the log for this PROC SQL, not part of the log).

Re: Compatibility between date types

AlexSP — Thu, 30 Sep 2021 16:48:41 GMT

Unfortunately due to organizational rules I cannot share such material. But I keep trying to resolve this issue of the date.

Re: Compatibility between date types

ballardw — Thu, 30 Sep 2021 17:04:52 GMT

@AlexSP wrote:

Unfortunately due to organizational rules I cannot share such material. But I keep trying to resolve this issue of the date.

Which specific material can you not share? Code? Variable names? or actual values?

Perhaps two small dummy data sets with only a couple of values of similar ranges and code to work them.

Otherwise, can't help. It's like medicine in the middle ages when the doctor sat outside the room a sick noblewoman was in asking her to describe the symptoms and "diagnosing" an illness.

Re: Compatibility between date types

PaigeMiller — Thu, 30 Sep 2021 17:10:08 GMT

Can't share the LOG?

Re: Compatibility between date types

Tom — Thu, 30 Sep 2021 17:32:41 GMT

So it looks like both values are datetime (number of seconds), but they do represent different values that are about 28 months apart.

529   data check;
530     dt1='03JAN2019:00:00:00.000'dt;
531     dt2=1935705600 ;
532     put (dt1 dt2) (=datetime19.);
533     put (dt1 dt2) (=comma20.);
534   run;

dt1=03JAN2019:00:00:00 dt2=04MAY2021:00:00:00
dt1=1,862,092,800 dt2=1,935,705,600

Why are you trying to change the type of the values by using DHMS() or DATEPART() functions?

If DTC already has number of seconds values then you would want to put it into the S part of the DHMS() function call, not the D part.

If you convert from number of seconds to number of days using the DATEPART() function then you will want to attach a date type format (DATE, YYMMDD, etc) to the variable and not a datetime type format.

Re: Compatibility between date types

AlexSP — Thu, 30 Sep 2021 19:26:05 GMT

PaigeMiller, I have not permission to share the LOG.
Ballarddw,
In Excel I have the dates like
03/05/2021
04/05/2021
* First select
I have date in the format 03MAY2021:00:00:00.000 (numeric, datetime 22.3 format)
* Second select (over the Excel)
I have date in the format 05/03/2021 (numeric, mmddyy10. format).

*but After UNION
I got the date in the format like 1935705600

Tom, I have used your solution but it did not work.

As I am new in SAS programming, I am reading all I can to understand how dates function works to be able to alter the date.

Re: Compatibility between date types

PaigeMiller — Thu, 30 Sep 2021 19:42:04 GMT

If you can't share the log, then I recommend you find someone at your company who can help you, since I assume he can come over to your office and see the log that way.

Also, it does NOT matter what you see in Excel. It is a mistake to look in Excel. You need to look in the SAS data sets and then you should be able to determine if your values are dates or date/times.

If you are getting 1935705600 then you have a datetime value, and you should format it as datetime22.3 (or similar), partial SQL code:

ajt.DTC_MOV format=datetime22.3

Re: Compatibility between date types

AlexSP — Thu, 30 Sep 2021 20:03:33 GMT

Analysts here don't know either. As there is an appreciation of IT professionals (which is good) old analysts who had more knowledge have already left.

I put this code ajt.DTC_MOV format=datetime22.3 in both selects. The result is in the correct format, but with the wrong result: 01JAN1960:06:19:28,000 instead 03MAY2021.

* Select 1: dac.dtc_credito format = datetime22.3 as d1
union
* Select 2: ajt.DTC_MOV format=datetime22.3 as d2
Result: 01JAN1960:06:19:28,000

Re: Compatibility between date types

AlexSP — Thu, 30 Sep 2021 20:05:31 GMT

Correcting

* Select 1: dac.dtc_credito format = datetime22.3 as d1
union
* Select 2: ajt.DTC_MOV format=datetime22.3 as d1
Result: 01JAN1960:06:19:28,000

Re: Compatibility between date types

AlexSP — Thu, 30 Sep 2021 20:31:48 GMT

Eureka!

Solution for this problem:

* Select 1: datepart (dac.dtc_credito) format = mmddyy10. as D1
UNION
* Select 2: ajt.DTC_MOV as D1
Result: 05/03/2021, as desired.
Thanks PaigeMiller, Tom, BallarDW for the help.

Re: Compatibility between date types

PaigeMiller — Thu, 30 Sep 2021 21:32:38 GMT

@AlexSP wrote:
Eureka!

Solution for this problem:

* Select 1: datepart (dac.dtc_credito) format = mmddyy10. as D1
UNION
* Select 2: ajt.DTC_MOV as D1
Result: 05/03/2021, as desired.
Thanks PaigeMiller, Tom, BallarDW for the help.

But this doesn't give you a date/time format as you have been asking for.

Nor does it seem to correspond to the question originally asked.