topic Re: How to apply DATE macro while connect to ODBC using proc sql in SAS Programming

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

Bikila — Wed, 06 Nov 2019 20:24:00 GMT

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,

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

r_behata — Wed, 06 Nov 2019 20:35:49 GMT

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;

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

Tom — Wed, 06 Nov 2019 20:49:54 GMT

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;

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

Bikila — Wed, 06 Nov 2019 21:41:32 GMT

Thanks, Tom.

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

hashman — Wed, 06 Nov 2019 22:09:16 GMT

@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.

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

Bikila — Wed, 06 Nov 2019 23:04:59 GMT

Thank you!