noted & thanks, ... View more

sorry typo: 'CRM_DATA_OUTBOX', i mean 'v_CRM_DATA_DAILY' ... View more

still cannot get the CRM_DATA_OUTBOX, somehow SAS select another table v_ISEE_STAFF_DATA, as shown in the logs. ... View more

If access has been granted, then the SAS code used is as above that I use? ... View more

the log:   1 2 3 LIBNAME X ODBC datasrc=CRMDATA user=HH09094X pwd=XXXXXXXXXXXXX; NOTE: Libref X was successfully assigned as follows: Engine: ODBC Physical Name: CRMDATA 4 5 %let date=%sysfunc(datetime())-2; 6 %let startdate=%sysfunc(intnx(dtmonth,&date,0,b)); 7 %let enddate=%sysfunc(intnx(dtmonth,&date,0,e)); 8 %put _user_; GLOBAL DATE 1995911318.348-2 GLOBAL ENDDATE 1995926399 GLOBAL STARTDATE 1993248000 GLOBAL SYSDBMSG GLOBAL SYSDBRC 0 9 10 proc sql; 11 connect using x; 12 create table aaaa as select * from connection to x 13 (select * from CRM_DATA_DAILY 14 where ticket_date between &startdate and &enddate); ERROR: CLI open cursor error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime. 15 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 9.68 seconds cpu time 0.10 seconds       ... View more

1 2 3 LIBNAME X ODBC datasrc=CRMDATA user=HH09094X pwd=XXXXXXXXXXXXX; NOTE: Libref X was successfully assigned as follows: Engine: ODBC Physical Name: CRMDATA 4 5 %let date=%sysfunc(datetime())-2; 6 %let startdate=%sysfunc(intnx(dtmonth,&date,0,b)); 7 %let enddate=%sysfunc(intnx(dtmonth,&date,0,e)); 8 %put _user_; GLOBAL DATE 1995911318.348-2 GLOBAL ENDDATE 1995926399 GLOBAL STARTDATE 1993248000 GLOBAL SYSDBMSG GLOBAL SYSDBRC 0 9 10 proc sql; 11 connect using x; 12 create table aaaa as select * from connection to x 13 (select * from CRM_DATA_DAILY 14 where ticket_date between &startdate and &enddate); ERROR: CLI open cursor error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime. 15 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 9.68 seconds cpu time 0.10 seconds ... View more

there NO ERROR on the Log my bro,  that the ENTIRE log,   the point is: what SAS code that i can use if i want to get only Ticket_Date on Mar2023 only ? and the filter of that period data i want to use sys date function, %let,    ... View more

code: %let date=%sysfunc(today())-2; %let dd =%sysfunc(putn(&date.,ddmmyy2.)); %let mm =%sysfunc(putn(&date.,mmddyy2.)); %let mmm =%sysfunc(substr(%sysfunc(putn(&date.,monyy.)),1,3)); %let yy =%sysfunc(putn(&date.,yymmdd2.)); %let yyyy =20&yy.; %let edate =%sysfunc(intnx(month,&date.,0,e)); %let edd =%sysfunc(putn(&edate.,ddmmyy2.)); LIBNAME X ODBC datasrc=CRMDATA user=zzzzz  pwd=xxxxx; proc sql outobs=3; connect using x; create table CRMDAILY5 as select * from connection to X ( select * from CRM_DATA_DAILY /*where convert(varchar, Ticket_Date, 111) between '2023/03/01' and '2023/03/31'*/ where convert(varchar, Ticket_Date, 111) between '&yyyy./&mm./01' and '&yyyy./&mm./&edd.'); quit;     log: NOTE: Libref X was successfully assigned as follows: Engine: ODBC Physical Name: CRMDATA NOTE: Table WORK.CRMDAILY5 created, with 0 rows and 45 columns. NOTE: PROCEDURE SQL used (Total process time): real time 10.09 seconds cpu time 0.07 seconds         ... View more

1. SQL Server   2.  if i run this code:   proc sql outobs=3; connect using x; create table CRMDAILY5 as select * from connection to X (select * from CRM_DATA_DAILY where convert(varchar, Ticket_Date, 111) between '2023/03/01' and '2023/03/31'); quit;   the log & result: WARNING: Statement terminated early due to OUTOBS=3 option. NOTE: Compressing data set WORK.CRMDAILY5 increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: Table WORK.CRMDAILY5 created, with 3 rows and 45 columns. NOTE: PROCEDURE SQL used (Total process time): real time 10.67 seconds cpu time 0.04 seconds     3. if i running using:  where convert(varchar, Ticket_Date, 111) between '&yyyy./&mm./01' and '&yyyy./&mm./&edd ');    the log: NOTE: Table WORK.CRMDAILY5 created, with 0 rows and 45 columns. NOTE: PROCEDURE SQL used (Total process time): real time 12.71 seconds cpu time 0.03 seconds           ... View more