Hell all,
I have a query that got to me looking something like this:
proc sql; select a.ID, A.DATE from TABLE a where A.DATE >= to_date('201810', 'YYYYMM') and A.DATE < to_date('201910', 'YYYYMM'); quit;
Since SAS doesn't recognize the to_date() function, I need to write it a different way. The date values come from a variable where 201810 means October 2018. How would y'all change the code? It needs to filter by dates falling between October 2018 and October 2019. Thanks in advance.
What is the formatting and appearance of the variable date? Is it numeric or character? If you don't know, please run PROC CONTENTS on data set TABLE and show us the relevant parts, and show us a screen capture or print of a few lines of variable DATE.
Can you not use the between operator?
proc sql;
select a.ID, a.DATE
from TABLE a
where (a.Date BETWEEN 201810 AND 201910);
quit;
proc sql;
select ID, DATE
from TABLE
where DATE between '201810' and '201910';
quit;
Assuming that your DATE variable is a SAS date, one possibility is to change the constants you are using into SAS date values:
proc sql;
select a.ID, A.DATE
from TABLE a
where A.DATE >= '01OCT2018'd and
and A.DATE < '01OCT2019'd;
quit;
But if your data are in SQL Server, things may be different. If your date value is actually a DATETIME or a DATETIME2 type, the data will come out as SAS datetime values, in which case you can use
proc sql;
select a.ID, A.DATE
from TABLE a
where A.DATE >= '01OCT2018:00:00:00'dt and
and A.DATE < '01OCT2019:00:00:00'dt;
quit;
Then, on the other hand, if your SQL Server variable is a DATE data type, SAS will return it as a character variable (deep sigh: When is SAS going to correct that?). Normally, I would then expect it to come out like e.g. '2019-11-31', but you might want to check, as it may depend on the national language settings of your systems. You could, of course, just go with a plain character comparison - another possibility is to use the DBSASTYPE data set option, which forces SAS to recognize it as a date:
proc sql;
select a.ID, A.DATE
from TABLE(DBSASTYPE=(DATE='DATE')) a
where A.DATE >= '01OCT2018'd and
and A.DATE < '01OCT2019'd;
quit;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.