DATA Step, Macro, Functions and more

Pass Date macro Variable into a PROC SQL Where statement

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Pass Date macro Variable into a PROC SQL Where statement

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.


Accepted Solutions
Solution
‎05-12-2017 02:42 AM
Super User
Posts: 6,936

Re: Pass Date macro Variable into a PROC SQL Where statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎05-12-2017 02:42 AM
Super User
Posts: 6,936

Re: Pass Date macro Variable into a PROC SQL Where statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: Pass Date macro Variable into a PROC SQL Where statement

Thanks for the quick reply.

 It actually worked.

Super User
Posts: 6,936

Re: Pass Date macro Variable into a PROC SQL Where statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 153 views
  • 0 likes
  • 2 in conversation