PROC SQL, can't filter datepart between date converted from numeric

Question

I have a SAS table sastable that I'm joining with a column from a SQL database to create a new table. However, I need to filter the sql.data with a date column from sastable. sastable.date is already in date time format, but sql.start and sql.end are stored in numeric YYYYMMDD format, such as '20231219'.

My code currently looks like this:

Proc sql;
Create Table newtable as
Select a.*,
   b.sqldate
From sastable as a
Left Join sql as b on a.id=b.id
where (datepart(a.date) between input(put(b.start, yymmdd10.),yymmdd10.) 
   and input(put(b.end, yymmdd10.), yymmdd10.)) 
   and b.void = '';
  

When run, the table comes up empty. Any guidance on how to fix this would be appreciated. Or if I'm not even on the right track.

Answer 1

When you say "but 'sql.start' and 'sql.end' are stored in numeric YYYYMMDD format" it makes no sense since SAS dates are just numbers. If it is the case, a Date, then no need to input and put. If not, the storage format is not what you think. To make it simple: if start and end are dates (no importance what informat and format are), your code become: datepart(a.date) between b.start and b.end

Answer 2

If START and END have integer values like 20,231,219 then you need to use the Z8. or plain old 8. format to convert them into strings that the YYMMDD8. informat can read.

input(put(b.end, Z8.), yymmdd8.)