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,
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;
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;
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;
Thanks, Tom.
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.
Thank you!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.