topic Re: how can i call %let on proc sql query? in SAS Programming

how can i call %let on proc sql query?

Handihusen — Fri, 31 Mar 2023 10:59:55 GMT

please help,

my 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.));

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;

the result is 0 observation

by if i write down: '2023/03/01' and '2023/03/31'

then i get the observation

question:

how the wright code to call the %let value, on this: '&yyyy./&mm./01' and '&yyyy./&mm./&edd '

thanks before,

Re: how can i call %let on proc sql query?

PaigeMiller — Fri, 31 Mar 2023 11:31:36 GMT

A couple of questions:

What type of database are you connecting to? Oracle, SQLServer, other?
Are you sure that the variable in the database that you have named VARCHAR is actually character and not numeric? How do you know, what is the evidence?
What is the exact error message in SAS — please provide the ENTIRE log for this PROC SQL, not just the error messages. Copy the log as text and paste it into the window that appears when you click on the </> icon

Re: how can i call %let on proc sql query?

Handihusen — Fri, 31 Mar 2023 11:50:57 GMT

1. SQL Server

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

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

Re: how can i call %let on proc sql query?

PaigeMiller — Fri, 31 Mar 2023 11:55:51 GMT

Regarding #2, the evidence you show indicates that the variable is numeric, and formatted with a numeric format DATETIME22.3. Thus anything you are trying to do with character strings will not work, and this is why you end up with zero observations in the output data set. Dates and date/times are treated in almost all databases — and you should do this in your SAS code — as numeric variables. As a result, I recommend you start over trying to code this with numeric dates, rather than character string dates.

Regarding #3, you didn't show me the ENTIRE log for PROC SQL as I requested. You did not follow the instructions I provided in #3. Please follow the instructions and show me the ENTIRE log.

Re: how can i call %let on proc sql query?

Handihusen — Fri, 31 Mar 2023 12:00:34 GMT

code:

%let date=%sysfunc(today())-2;

LIBNAME X ODBC datasrc=CRMDATA user=zzzzz pwd=xxxxx;

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

Re: how can i call %let on proc sql query?

PaigeMiller — Fri, 31 Mar 2023 12:15:32 GMT

I asked: "What is the exact error message in SAS — please provide the ENTIRE log for this PROC SQL, not just the error messages. Copy the log as text and paste it into the window that appears when you click on the </> icon"

This is not the ENTIRE log which I asked for, and you did not provide it following the instructions I gave.

Re: how can i call %let on proc sql query?

PaigeMiller — Fri, 31 Mar 2023 12:36:00 GMT

OK based on your answer to #2, and treating your variable as numeric, this should work

%let date=%sysfunc(datetime())-2;
%let startdate=%sysfunc(intnx(dtmonth,&date,0,b));
%let enddate=%sysfunc(intnx(dtmonth,&date,0,e));
proc sql;
    connect using x;
    create table aaaa as select * from connection to x 
    (select * from CRM_DATA_DAILY
        where ticket_date between &startdate and &enddate);
quit;

Re: how can i call %let on proc sql query?

Handihusen — Fri, 31 Mar 2023 12:30:46 GMT

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,

Re: how can i call %let on proc sql query?

Handihusen — Fri, 31 Mar 2023 13:22:06 GMT

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

Re: how can i call %let on proc sql query?

Handihusen — Fri, 31 Mar 2023 13:25:15 GMT

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

Re: how can i call %let on proc sql query?

PaigeMiller — Fri, 31 Mar 2023 13:30:25 GMT

Connection to X

That needs to be fixed to whatever the correct connection is