How to apply DATE macro while connect to ODBC using proc sql

[Bikila]

Hello,

I was writing DATE macro  while connecting to SQL server using ODBC. 

I wrote the following code: 

data _null_;
START_DATE= intnx ('month',today(),-2,'B'); 
END_DATE= intnx ('month',today(),-1,'E'); 
call symput ('S_DATE', START_DATE);
Call Symput ('E_DATE', END_DATE);
run;
proc sql;
connect to odbc as ALIAS
(datasrc=SQLSERVER user=USER password=PWD);
create table DATASET as
select * from connection to ALIAS
(SELECT distinct *
FROM DATABASE.dbo.TABLE
WHERE  DOS_FROM >= &S_DATE
)
;
QUIT;

I got the following error:

ERROR: CLI describe error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: date is incompatible with
smallint : [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.

Note: the variable DOS_FROM in the sql database is a format of date9.(DDMMMYYYY type). 

Any help is appreciated. 

Regards,

[r_behata]

Try :

data _null_;
START_DATE= intnx ('month',today(),-2,'B'); 
END_DATE= intnx ('month',today(),-1,'E'); 
call symputx ('S_DATE', quote(put(START_DATE,yymmddd10.)));
Call Symputx ('E_DATE', quote(put(END_DATE,yymmddd10.))); ;
run;

[Tom · Accepted solution]

What syntax does your remote database want?  

Here is an example to generate strings like:

'2019-11-06'

data _null_;
  START_DATE= intnx ('month',today(),-2,'B'); 
  END_DATE= intnx ('month',today(),-1,'E'); 
  call symputx ('S_DATE', quote(put(START_DATE,yymmdd10.),"'"));
  call symputx ('E_DATE', quote(put(END_DATE,yymmdd10.),"'"));
run;
proc sql;
connect to odbc as ALIAS (datasrc=SQLSERVER user=USER password=PWD);
create table DATASET as
  select * from connection to ALIAS
  (SELECT distinct *
   FROM DATABASE.dbo.TABLE 
   WHERE  DOS_FROM >= DATE &S_DATE
  )
;
QUIT;

[Bikila]

Thanks, Tom.

[hashman]

@Bikila:

You don't need to write a macro. The macro variables you need can be obtained using the following macro expressions:

%let sdate = %str(%')%sysfunc(intnx(mon,%sysfunc(date()),-2,B),yymmdd10)%str(%') ;                                                      
%let edate = %str(%')%sysfunc(intnx(mon,%sysfunc(date()),-1,E),yymmdd10)%str(%') ; 

If you %PUT them in the log, you'll see:

%put &=sdate &=edate ;
SDATE='2019-09-01' EDATE='2019-10-31'

And you don't even have to assign the expressions to macro variables, as they can be used directly in the query, like so:

DOS_FROM >= %str(%')%sysfunc(intnx(mon,%sysfunc(date()),-2,B),yymmdd10)%str(%')

OTOH, since you're using SQL Server specific language via explicit Pass-Thru, you can use SQL Server functions GETDATE(), DATEADD, CAST, EOMONTH, and so on to generate your shifted dates in the query itself.

Kind regards

Paul D.

[Bikila]

Thank you!