Date formatting is incorrect

[Sandeep77]

Hi all,

I am running a code which has date stated in the %let statement. But it shows some error after running the code. Can you please check if my date format is incorrect? Thanks.

%let startTrace = 01SEP2022; /*<-- Input date  */
%let Start_VAR_DT = '01SEP2022:00:00:00.000'dt;
%let END_VAR_DT = '30SEP2022:23:59:59.999'dt;
proc sql;
 connect to oledb (provider=sqlncli11.1
 properties = ("Integrated Security" = SSPI
 "Persist Security Info" = False
 "Initial Catalog" = pinsys
 prompt = NO
 "Data Source" = 'ELECTRA'
 read_lock_type = no));
create table Collections_Since_Release as
	select distinct
 				t1.*,
			sum(t2.tx_amount) as Collected_Since_Release
		from All_Accounts_In_Trace /* YOUR DATA SET */as t1
			left join (select * from connection to oledb 
						(select debt_code,
								paymentdate,
								tx_amount,
								dtcompany,
								tran_code
 				from [Pinsys].[dbo].[debt_trans]
 where paymentdate >= &Start_trace
						and tran_code like 'DR%'
						and tran_code not in ('DR3109','DR3110','DR3111','DR3113','DR4000','DR4001','DR4010','DR4011',
												'DR4050','DR4060','DR4070','DR7000','DR3105','DR3107','DR3108');)) 
				as t2 on (t1.debt_code = t2.debt_code and t1.&Start_VAR_DT <= t2.paymentdate <= t1.&END_VAR_DT)

				group by t1.debt_code, t1.&Start_VAR_DT
	 				order by t1.debt_code, t1.&Start_VAR_DT;
quit;

Error log:
29         %let startTrace = 01SEP2022; /*<-- Input date  */
30         %let Start_VAR_DT = '01SEP2022:00:00:00.000'dt;
31         %let END_VAR_DT = '30SEP2022:23:59:59.999'dt;


32         proc sql;
33          connect to oledb (provider=sqlncli11.1
34          properties = ("Integrated Security" = SSPI
35          "Persist Security Info" = False
36          "Initial Catalog" = pinsys
37          prompt = XX
38          "Data Source" = 'ELECTRA'
39          read_lock_type = no));

40         create table Collections_Since_Release as
41         	select distinct
42          				t1.*,
43         			sum(t2.tx_amount) as Collected_Since_Release
44         		from All_Accounts_In_Trace /* YOUR DATA SET */as t1
45         			left join (select * from connection to oledb
46         						(select debt_code,
47         								paymentdate,
48         								tx_amount,
49         								dtcompany,
50         								tran_code
51          				from [Pinsys].[dbo].[debt_trans]
52          where paymentdate >= &Start_trace
WARNING: Apparent symbolic reference START_TRACE not resolved.
53         						and tran_code like 'DR%'
2                                                          The SAS System                            13:24 Monday, November 14, 2022

54         						and tran_code not in ('DR3109','DR3110','DR3111','DR3113','DR4000','DR4001','DR4010','DR4011',
55         												'DR4050','DR4060','DR4070','DR7000','DR3105','DR3107','DR3108');))
56         				as t2 on (t1.debt_code = t2.debt_code and t1.&Start_VAR_DT <= t2.paymentdate <= t1.&END_VAR_DT)
NOTE: Line generated by the macro variable "START_VAR_DT".
56          t1.'01SEP2022:00:00:00.000'dt
              __________________________
              22
              200
NOTE: Line generated by the macro variable "END_VAR_DT".
56          t1.'30SEP2022:23:59:59.999'dt
              __________________________
              22
              200
ERROR 22-322: Syntax error, expecting one of the following: a name, *.  

ERROR 200-322: The symbol is not recognized and will be ignored.

57         
58         				group by t1.debt_code, t1.&Start_VAR_DT
59         	 				order by t1.debt_code, t1.&Start_VAR_DT;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "START_VAR_DT".
59          t1.'01SEP2022:00:00:00.000'dt
              __________________________
              22
              76
ERROR 22-322: Syntax error, expecting one of the following: a name, *.  

ERROR 76-322: Syntax error, statement will be ignored.

60         quit;

[LinusH]

1. You use StartTrace and Start_Trace interchangeably.
2. You refer to a datetime constant as column name (prefixed with table alias), that's not correct syntax.

[AMSAS]

The issue isn't the format it's the SQL code:

as t2 on (t1.debt_code = t2.debt_code and t1.&Start_VAR_DT <= t2.paymentdate <= t1.&END_VAR_DT)

You are referencing a variable on the table t1 but the macro variable resolves to a date time constant, this doesn't make sense

as t2 on (t1.debt_code = t2.debt_code and t1.'01SEP2022:00:00:00.000'dt <= t2.paymentdate <= t1.'30SEP2022:23:59:59.999'dt)

I assume you really want to remove the references to t1:

as t2 on (t1.debt_code = t2.debt_code and &Start_VAR_DT <= t2.paymentdate <= .&END_VAR_DT)

[PaigeMiller]

t1.&Start_VAR_DT

when the code executes, this turns into

t1.'01SEP2022:00:00:00.000'dt

which is not valid legal SAS code. What should go after t1. in PROC SQL??? What could you put after t1. to make this working legal valid SAS code???

[Sandeep77]

Thank you for pointing out where it's causing the issue. I am not very sure of it. Do I have to use the date manually instead of &Start_VAR_DT?

[PaigeMiller]

You are feeding PROC SQL invalid code. You can't just arbitrarily place dates in a macro variable and then place them into SQL anywhere you want and expect them to work.

If you type 

t1.variablename

in PROC SQL, this indicates that SQL should use the variable named (in this case) VARIABLENAME from data set T1.

If you type 

t1.'01SEP2022:00:00:00.000'dt

SQL thinks it should be looking for a variable named '01SEP2022:00:00:00.000'dt in data set T1. Do you have a variable named '01SEP2022:00:00:00.000'dt in data set T1????? Yes or no? 

[PaigeMiller]

Adding: the usual advice is for people to develop working code for one or two instances of this problem without macros and without macro variables. The code you have created indicates you did not do this. If you do first create working code without macros and without macro variables, it is much more likely that you can then turn this into working macro code and the mistake you have made here likely would not happen.

So, @Sandeep77 , most people ignore the advice above, and struggle and struggle and struggle and struggle with writing macros. Don't be one of those people. Please from now on, follow the advice above, instead of ignoring the advice above as most people do.