Using Query Builder how to change datetime to Date9. format

[FLCrime]

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 🙂

[novinosrin]

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

[FLCrime]

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

[novinosrin · Accepted solution]

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

[FLCrime]

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

[novinosrin]

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

[Reeza]

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

---

@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 🙂

---