topic Re: Running Program For The last 45 days in New SAS User

Running Program For The last 45 days

aperansi — Fri, 16 Nov 2018 19:45:54 GMT

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.

Re: Running Program For The last 45 days

r_behata — Fri, 16 Nov 2018 20:18:24 GMT

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;

Re: Running Program For The last 45 days

r_behata — Fri, 16 Nov 2018 20:26:11 GMT

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;

Re: Running Program For The last 45 days

Ksharp — Sat, 17 Nov 2018 10:37:52 GMT

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

Re: Running Program For The last 45 days

Tom — Sat, 17 Nov 2018 16:11:23 GMT

It is probably a lot easy to play with getting the date and datetime value in a data step. You can then use CALL SYMPUTX() to generate your macro variables.

data _null_;
  end=today();
  start=end-45 ;
  end_dt=dhms(end,0,0,0);
  start_dt=dhms(start,0,0,0);
  put (start end) (= yymmdd10.) (start_dt end_dt) (= datetime19.);
  call symputx('startdate',quote(put(start,yymmdd10.),"'"));
  call symputx('enddate',quote(put(end,yymmdd10.),"'"));
  call symputx('start_dt',quote(cats(put(start_dt,datetime19.)))||'dt');
  call symputx('end_dt',quote(cats(put(end_dt,datetime19.)))||'dt');
run;

Results:

start=2018-10-03 end=2018-11-17 start_dt=03OCT2018:00:00:00 end_dt=17NOV2018:00:00:00

555  %put &=startdate;
STARTDATE='2018-10-03'
556  %put &=enddate;
ENDDATE='2018-11-17'
557  %put &=start_dt;
START_DT="03OCT2018:00:00:00"dt
558  %put &=end_dt;
END_DT="17NOV2018:00:00:00"dt

Note that if you are going to use those date or datetime values in SAS code then there is no need to format them in human friendly ways. You can just use the underlying integer values.

  call symputx('startdate_easy',start);
  call symputx('enddate_easy',end);
  call symputx('start_dt_easy',start_dt);
  call symputx('end_dt_easy',end_dt);

560  %put &=startdate_easy;
STARTDATE_EASY=21460
561  %put &=enddate_easy;
ENDDATE_EASY=21505
562  %put &=start_dt_easy ;
START_DT_EASY=1854144000
563  %put &=end_dt_easy ;
END_DT_EASY=1858032000

Re: Running Program For The last 45 days

aperansi — Mon, 26 Nov 2018 20:38:30 GMT

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?

Re: Running Program For The last 45 days

r_behata — Mon, 26 Nov 2018 20:47:18 GMT

Please post your SQL Query.

Re: Running Program For The last 45 days

Tom — Mon, 26 Nov 2018 20:47:30 GMT

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.

Re: Running Program For The last 45 days

aperansi — Mon, 26 Nov 2018 21:00:48 GMT

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

Re: Running Program For The last 45 days

r_behata — Mon, 26 Nov 2018 21:05:10 GMT

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

Re: Running Program For The last 45 days

aperansi — Mon, 26 Nov 2018 21:08:31 GMT

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.

Re: Running Program For The last 45 days

r_behata — Mon, 26 Nov 2018 21:37:38 GMT

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;

Re: Running Program For The last 45 days

aperansi — Mon, 26 Nov 2018 21:40:31 GMT

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;

Re: Running Program For The last 45 days

r_behata — Mon, 26 Nov 2018 21:54:20 GMT

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;

Re: Running Program For The last 45 days

aperansi — Mon, 26 Nov 2018 22:00:39 GMT

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.

Re: Running Program For The last 45 days

aperansi — Mon, 26 Nov 2018 22:01:00 GMT

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