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.
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.
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.
Thanks for the quick reply.
It actually worked.
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.