Pass Date macro Variable into a PROC SQL Where statement

[deepika2]

I am trying to write a PROC SQL statement which is extracting data from SQL Server based on some date criteria.

%let ExamStartDate = 01APR2017;
%let ExamEndDate = 01MAY2017;

data _null_;

call symputx('sdate',put("&ExamStartDate."d,yymmdd10.));
call symputx('edate',put("&ExamEndDate."d,yymmdd10.));

run;

proc sql;
connect to odbc (dsn='aa' user=user pw=pw);
create table temp1 as select * from connection to odbc
(
SELECT cnd.firstName, cnd.middleName, cnd.lastName, cd.calendarDate as date
FROM candidate cnd inner join CalendarDate cd with (NOLOCK) on cd.calendarDateId=cnd.eventDateId
and cd.calendarDate >= convert(datetime,&sdate.)
);
quit;

When I run the code, it gives me all records and does not filter out the data. Can anyone suggest what am I doing wrong here.

Thanks for the help.

[Kurt_Bremser · Accepted solution]

Use the SASTRACE= system option to get more information back from the data source.

It might be that the SQL Server expects dates in a format like this:

data _null_;
call symputx('sdate','"'!!put("&ExamStartDate."d,yymmddd10.)!!'"');
call symputx('edate','"'!!put("&ExamEndDate."d,yymmddd10.)!!'"');
run;

so the string that is handed over will be "2017-04-01" (including the quotes). And you might need to supply timestamps instead of dates, as you use the type datetime in the convert, if I interpret that correctly.

[deepika2]

Thanks for the quick reply.

 It actually worked.

[Kurt_Bremser]

Out of curiousity: did my step as written do the trick, or did you have to adapt it? Mind that I have absolutely no experience with SQL Server passthrough, and very little with passthrough to UDB/DB2. Our communication with the database is done solely by flatfiles.