topic Re: Date Format 01JAN2023 to 2023-01-01 Using SQL in SAS Enterprise Guide

Date Format 01JAN2023 to 2023-01-01 Using SQL

paulskie08 — Fri, 31 Mar 2023 17:10:28 GMT

Hi,

First off, I am very new to SAS and SQL. I am trying to convert the date column on my table from 01JAN2023 to 2023-01-01 on my PROC SQL statement but I keep on getting an error message. I tried the FORMAT and CONVERT options but no go. Here's how I'm doing it:

Select query_date, query_type

from call_database

where query_date >= '01JAN2023'

What should be the correct format for this statement? TIA!

Re: Date Format 01JAN2023 to 2023-01-01 Using SQL

Kurt_Bremser — Fri, 31 Mar 2023 17:14:56 GMT

For the WHERE clause, you need to use a valid date literal:

'01jan2023'd

Re: Date Format 01JAN2023 to 2023-01-01 Using SQL

PaigeMiller — Fri, 31 Mar 2023 17:16:14 GMT

If all you want is to use the WHERE statement properly in SQL for this problem, use this (assuming query_date is a numeric variable that is an actual SAS date value).

where query_date >= '01JAN2023'd

What are typical values of QUERY_DATE?

Also, this seems to have nothing to do with your title, and I get the feeling that somewhere along the way, the question in the title is relevant, but I can't see right now what the relevance is.

Re: Date Format 01JAN2023 to 2023-01-01 Using SQL

paulskie08 — Fri, 31 Mar 2023 17:17:00 GMT

Thanks for the response. I did try this too, but I got this error:

Syntax error: expected something between a string or a Unicode character literal and the word 'd'.

Re: Date Format 01JAN2023 to 2023-01-01 Using SQL

PaigeMiller — Fri, 31 Mar 2023 17:20:08 GMT

Show us the complete log for this PROC SQL. That's all of the log for PROC SQL, every single line in the log for PROC SQL, not just the error messages. Never show us error messages only and expect us to know what happened.

Re: Date Format 01JAN2023 to 2023-01-01 Using SQL

paulskie08 — Fri, 31 Mar 2023 17:21:09 GMT

I hovered over the query_date variable and it's a SAS date value.

All the values under query_date are in the same format (i,e, 29JUL2021, 18MAY2021, 01SEP2022)

I was initially trying to have the query_date output as 2023-01-01, but as long as I can show that all the dates are equal or greater than 01JAN2023, I should be good. Sorry if I didn't word it properly.

Re: Date Format 01JAN2023 to 2023-01-01 Using SQL

paulskie08 — Tue, 04 Apr 2023 15:52:34 GMT

Thank you, Paige. For some reason, it was an issue with the column name. I renamed the column and the syntax worked.

Re: Date Format 01JAN2023 to 2023-01-01 Using SQL

Tom — Tue, 04 Apr 2023 16:27:29 GMT

@paulskie08 wrote:

I hovered over the query_date variable and it's a SAS date value.

All the values under query_date are in the same format (i,e, 29JUL2021, 18MAY2021, 01SEP2022)

I was initially trying to have the query_date output as 2023-01-01, but as long as I can show that all the dates are equal or greater than 01JAN2023, I should be good. Sorry if I didn't word it properly.

A FORMAT in SAS is just instructions for how to convert the values stored in the variable into text. The format attached to the variable is independent of the values stored.

A data value in SAS is a number representing the number of days since the start of 1960. If you display it using the DATE9. format it will look like 29JUL2021. If you display it with the YYMMDD10. format it will look like 2023-07-29.

1440  data _null_;
1441   date='29JUL2023'd ;
1442   put date= comma12. +1 date date9. +1 date yymmdd10. ;
1443  run;

date=23,220  29JUL2023 2023-07-29

In SQL if you want to attach a format to variable use the FORMAT= option after the variable definition.

create table want as 
  select id, datevar format=yymmdd10.
  from have
;