I currently have a program that I am running that I manually input the start date and end date with a variable I create at the beginning of the code. I would like to avoid doing this in the future, and instead create a date variable that would detect the current date, and run the program with a date that is 45 days in the past.
Any advice on how to proceed would be great.
Here is the current date variable that I declare:
%let startdate = '2018-09-23';
%let enddate = '2018-11-01';
%let start_dt = '23sep2018:00:00:00'dt;
%let end_dt = '01nov2018:00:00:00'dt;
Thank you very much for your time.
data _null_;
sdt=put(today(),yymmdd10.);
edt=put(intnx('days',today(),45),yymmdd10.);
s_dt=cats(put(today(),date9.),':00:00:00');
e_dt=cats(put(intnx('days',today(),45),date9.),':00:00:00');
call symputx('sdt',quote(strip(sdt)));
call symputx('edt',quote(strip(edt)));
call symputx('s_dt',strip(s_dt));
call symputx('e_dt',strip(e_dt));
run;
%put &=sdt &=edt &=s_dt &=e_dt;
%let startdate = &sdt;
%let enddate = &edt;
%let start_dt = "&s_dt"dt;
%let end_dt = "&e_dt"dt;
Slightly Modified Version :
data _null_;
sdt=put(today(),yymmdd10.);
edt=put(intnx('days',today(),45),yymmdd10.);
s_dt=cats(put(today(),date9.),':00:00:00');
e_dt=cats(put(intnx('days',today(),45),date9.),':00:00:00');
call symputx('startdate',quote(strip(sdt)));
call symputx('enddate ',quote(strip(edt)));
call symputx('s_dt',strip(s_dt));
call symputx('e_dt',strip(e_dt));
run;
%put &=startdate &=enddate &=s_dt &=e_dt;
%let start_dt = "&s_dt"dt;
%let end_dt = "&e_dt"dt;
%put &=start_dt &=end_dt;
I switched over to what you have below and it appears to be causing errors now. One of the error states "CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '2018-11-26'. : [Microsoft][ODBC SQL
Server Driver][SQL Server]Invalid column name '2019-01-10'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
could not be prepared."
I think it has something to do with the way the date is formatted. Could you help me understand how to fix this?
Please post your SQL Query.
Here is the Proc Sql.
proc sql;
CONNECT TO odbc as myconn (noprompt="server=canldsassqlrpt;DRIVER=SQL Server;Trusted_Connection=yes;database=customerScoring");
CREATE TABLE TUnionLD AS
select *
from connection to myconn
(
SELECT AD.AGENCY_DATA_KEY AS api_call,
AD.DATE_ENTERED AS api_datetime
FROM CUSTOMERSCORING.DBO.AGENCYDATA AD
WHERE AD.AGENCY_MASTER_KEY in (68) AND AD.DATA_ERROR = 0 AND
AD.DATE_ENTERED >= &startdate. AND AD.DATE_ENTERED < &enddate.
)
;
disconnect from myconn;
quit;
proc sql;
create table TULD as
select datepart(api_datetime) as api_date format yymmdd10.,
count(distinct api_call) as Count format comma20.
from TUnionLD
group by api_date
;
quit;
data TUCreditLDDS;
set TULD;
length mid $15 product $30 vendor $30;
vendor = 'TransUnion';
mid = '0720F2002474';
product = 'Credit Report';
run;
In case you need this, here is a screenshot of what the data looks like in SQL
Could you post this section of the log as well. Want to see the resolved values of the macro variables.
Here is the Log Summary:
And here is the information in the Log:
GOPTIONS ACCESSIBLE;
23 proc sql;
24 CONNECT TO odbc as myconn
24 ! (noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX);
25
26 CREATE TABLE TUnionLD AS
27 select *
28 from connection to myconn
29 (
30 SELECT AD.AGENCY_DATA_KEY AS api_call,
31 AD.DATE_ENTERED AS api_datetime
32 FROM CUSTOMERSCORING.DBO.AGENCYDATA AD
33 WHERE AD.AGENCY_MASTER_KEY in (68) AND AD.DATA_ERROR = 0 AND
34 AD.DATE_ENTERED >= &startdate. AND AD.DATE_ENTERED < &enddate.
35
36 )
37 ;
ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '2018-11-26'. : [Microsoft][ODBC SQL
Server Driver][SQL Server]Invalid column name '2018-10-12'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
could not be prepared.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
38 disconnect from myconn;
NOTE: Statement not executed due to NOEXEC option.
39 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.00 seconds
40 proc sql;
41 create table TULD as
42 select datepart(api_datetime) as api_date format yymmdd10.,
43 count(distinct api_call) as Count format comma20.
44 from TUnionLD
2 The SAS System 13:07 Monday, November 26, 2018
45 group by api_date
46 ;
ERROR: Function DATEPART requires a numeric expression as argument 1.
ERROR: Character expression requires a character format.
ERROR: The following columns were not found in the contributing tables: api_call, api_datetime.
The SQL Pass-thru query is expecting to have values for startdate and enddate. Ideally the startdate and enddate should resolve with the quotes around them for example as '2018-08-01 00:00:00' and '2018-11-26 00:00:00' .
From the Log it is very difficult to say if this is the case. I suspect that though the macro variables are resolved they are resolved without the quotes '' around so SQL server is treating them as columns instead as constants.
enable the below option an try again to see more details in the log.
options symbolgen;
I ran the code with the options you suggested, and here is the log:
26 CREATE TABLE TUnionLD AS
27 select *
28 from connection to myconn
29 (
30 SELECT AD.AGENCY_DATA_KEY AS api_call,
31 AD.DATE_ENTERED AS api_datetime
32 FROM CUSTOMERSCORING.DBO.AGENCYDATA AD
33 WHERE AD.AGENCY_MASTER_KEY in (68) AND AD.DATA_ERROR = 0 AND
34 AD.DATE_ENTERED
SYMBOLGEN: Macro variable STARTDATE resolves to "2018-11-26"
34 ! >= &startdate. AND AD.DATE_ENTERED < &enddate.
SYMBOLGEN: Macro variable ENDDATE resolves to "2018-10-12"
35
36 )
37 ;
ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '2018-11-26'. : [Microsoft][ODBC SQL
Server Driver][SQL Server]Invalid column name '2018-10-12'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
could not be prepared.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
38 disconnect from myconn;
NOTE: Statement not executed due to NOEXEC option.
39 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.08 seconds
cpu time 0.00 seconds
40 proc sql;
2 The SAS System 13:07 Monday, November 26, 2018
41 create table TULD as
42 select datepart(api_datetime) as api_date format yymmdd10.,
43 count(distinct api_call) as Count format comma20.
44 from TUnionLD
45 group by api_date
46 ;
ERROR: Function DATEPART requires a numeric expression as argument 1.
ERROR: Character expression requires a character format.
ERROR: The following columns were not found in the contributing tables: api_call, api_datetime.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
47 quit;
I am not a SQL Server expert but I think the way it treats the date constant in a double quote and single quote and without quote differently.
Try doing this and see if it works.
data _null_;
sdt=put(today(),yymmdd10.);
edt=put(intnx('days',today(),45),yymmdd10.);
s_dt=cats(put(today(),date9.),':00:00:00');
e_dt=cats(put(intnx('days',today(),45),date9.),':00:00:00');
call symputx('startdate',cats("'",strip(sdt),"'"));
call symputx('enddate ',cats("'",strip(edt),"'"));
call symputx('s_dt',cats("'",strip(s_dt),"'"));
call symputx('e_dt',cats("'",strip(e_dt),"'"));
run;
%put &=startdate &=enddate &=s_dt &=e_dt;
From the error message the database SQL parser thinks that by '2018-11-26' you are trying to refer to a variable instead of a constant value.
Show the actual code you submitted.
%let startdate = "%sysfunc(putn(%eval(%sysfunc(today())-45),yymmdd10.))";
%let enddate = "%sysfunc(today(),yymmdd10.)";
%put &startdate &enddate;
%let start_dt ="%sysfunc(putn(%eval(%sysfunc(today())-45),date9.)):00:00:00"dt;
%let end_dt = "%sysfunc(today(),date9.):00:00:00"dt;
%put &start_dt &end_dt;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.