I believe it is because your SQL server does not use the same coding for dates as SAS. You should try converting the dates to something the SQL server can use, either as text, or actually converting the date numerically; going via text is usually easiest and most transparent.

Try storing 05JAN2008 in your macro variable instead of the SAS numeric equivalent. Or whatever format your SQL expects (some expect as above, some might expect something different, like 01052008 or whatnot).

-Joe

On Thu, May 14, 2009 at 4:08 PM, Shaheen <siqubal@gmail.com> wrote:

> Hi, > I am not able to pass date as a parameter into a store proc I am > calling... when I hard coded the date, store proc returns data but > when I pass two variable which is suppose to have dates, it does not > work. > > Please take a look at my code and help: It crashes on exec line > > Log Result > __________ > 1757 exec USP_FilterReturnSecurityAndGevsIdGevsDataHist > 1757! 377,16166,&AlphaDateFrom,&AlphaDateTo,3 > SYMBOLGEN: Macro variable ALPHADATEFROM resolves to 16801 > SYMBOLGEN: Macro variable ALPHADATETO resolves to 16921 > > > Program > ________ > > DATA _NULL_; > set InvSys.Parameters; > call symput('AlphaDateFrom', AlphaDateFrom); > call symput('AlphaDateTo', AlphaDateTo); > call symput('FilterID', FilterID); > call symput('RebalFreq', RebalFreq); > call symput('RetType', RetType); > call symput('RetCurr', RetCurr); > call symput('SelMethod', SelMethod); > call symput('WeightMethod', WeightMethod); > call symput('PersonID', PersonID); > run; > > PROC SQL; > CONNECT TO ODBC (DSN='UAT'); > CREATE TABLE StagingTable AS > SElECT * FROM CONNECTION TO ODBC > ( > exec USP_DataHist 377,16166,&AlphaDateFrom,&AlphaDateTo,3 > ); > disconnect from ODBC; > quit; >