topic Re: Using Query Builder how to change datetime to Date9. format in SAS Enterprise Guide

Using Query Builder how to change datetime to Date9. format

FLCrime — Thu, 18 Jun 2020 17:05:22 GMT

I am incredibly new to sas or any database programming. I use SAS EG as I find it easier to work with. Question, how can I change my datetime column into a Date9. format so I can match using these dates? I would like to do this using the query builder if possible. When I use the datepart function in the advanced expressions I end up with negative numbers on my new calculated column.

Thanks 🙂

Re: Using Query Builder how to change datetime to Date9. format

novinosrin — Thu, 18 Jun 2020 17:14:44 GMT

are there any dates before 01jan1960? Did you format the date?

Re: Using Query Builder how to change datetime to Date9. format

FLCrime — Thu, 18 Jun 2020 17:18:32 GMT

Image below is all I know at this point. This is prior to me attempting the calculated column.

Re: Using Query Builder how to change datetime to Date9. format

Reeza — Thu, 18 Jun 2020 17:22:36 GMT

You can have negative dates so SAS is likely correct.

SAS stores dates/datetimes as a number from a reference date, in this case January 1, 1960. So any dates before Jan 1, 1960 are negative. Apply a date format to the field and SAS will show the data correctly.

Here's a great, but longer and in depth, reference for dates and times in SAS. I highly recommend reading this if you're working with dates and datetimes extensively.
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

@FLCrime wrote:

I am incredibly new to sas or any database programming. I use SAS EG as I find it easier to work with. Question, how can I change my datetime column into a Date9. format so I can match using these dates? I would like to do this using the query builder if possible. When I use the datepart function in the advanced expressions I end up with negative numbers on my new calculated column.

Thanks 🙂

Re: Using Query Builder how to change datetime to Date9. format

novinosrin — Thu, 18 Jun 2020 17:22:40 GMT

Okay:

1. Query builder

2. Add table

3. Choose columns

4. Add new computed column

5. Advanced expression

6. Enter an expression: datepart(your_datetime)

7. Modify additional options-- change the format to datew.d and choose 9

8. Finish

Now, run

Re: Using Query Builder how to change datetime to Date9. format

FLCrime — Thu, 18 Jun 2020 17:26:00 GMT

Thank you so much. It always turn out to be something super simple that I missed or had no knowledge of.

Re: Using Query Builder how to change datetime to Date9. format

novinosrin — Thu, 18 Jun 2020 17:28:13 GMT

No worries. It's one of those days. I must confess I still do not know what a computer,an operating system is. lol