Use six digit date variable to filter results

[Alexander2]

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.

[PaigeMiller]

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.

[Krueger]

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;

[ed_sas_member]

proc sql;
	select ID, DATE
	from TABLE
	where DATE between '201810' and '201910';
quit;

[s_lassen]

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;