Running Program For The last 45 days

[aperansi]

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. 

[r_behata]

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; 

[r_behata]

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;

[aperansi]

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?

[r_behata]

Please post your SQL Query.

[aperansi]

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

[r_behata]

Could you post this section of the log as well. Want to see the resolved values of the macro variables.

[aperansi]

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.

[r_behata]

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;

[aperansi]

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;

[r_behata]

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;

[aperansi]

I appreciate your help with this Behata. I re ran the code with your edits about, but it's still not functioning the way that we need it to unfortunately. When I review the data sets created, there are no observations in them at all.

[Tom]

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.

[aperansi]

I submitted the code, and the log messages above. Would you mind taking a look when you have a moment?

[Ksharp]

%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;