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!
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.
For the WHERE clause, you need to use a valid date literal:
'01jan2023'd
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'.
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.
Thank you, Paige. For some reason, it was an issue with the column name. I renamed the column and the syntax worked.
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.
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.
@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
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.