Changing the date format

[Sandeep77]

Hi all,

I have a written a code where the tx_date is in DATETIME22.3 (21NOV2006:00:00:00.000) format and I have set Start_trace and End Trace in libraries which is in DATE9. (15MAR2022) format. In my code, I am getting error the date format is not equal. So I used the Datepart function but still shows the same error. Can you please suggest how to convert the date in the same pattern?

proc sql; 
create table Trace_Stamped as 
	select distinct debt_code,
DATEPART(tx_date) format date9. as tx_date
from p2scflow.debt_trans 
			where tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748') 
and tx_date >= &Start_trace  and
              tx_date <= &End_Trace 	;
run; 

Error log:
30         proc sql;
31         create table Trace_Stamped as
32         	select distinct debt_code,
33         
34         					DATEPART(tx_date) format date9. as tx_date
35         					
36         		from p2scflow.debt_trans
37         			where tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748')
38         				and tx_date >= &Start_trace  and
39                       tx_date <= &End_Trace
40         			  	
41         ;
ERROR: Expression using greater than or equal (>=) has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL WHERE clause optimization.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
42         run;

[PaigeMiller]

---

@Sandeep77 wrote:

Hi all,

I have a written a code where the tx_date is in DATETIME22.3 (21NOV2006:00:00:00.000) format and I have set Start_trace and End Trace in libraries which is in DATE9. (15MAR2022) format. In my code, I am getting error the date format is not equal.

---

You are getting an error that has components "of different data types" -- in other words, you are trying to compare numeric to character, and you can't do this. This has nothing to do with formats.

We do not know, so please tell us, what an actual value of tx_date is, and what the actual value of &start_trace is. We also need to know, from PROC CONTENTS or otherwise, whether tx_date is numeric or character.

[Sandeep77]

I am pulling tx_date from debt_trans table and tx_date is numeric. Start_trace I have just used in the library like %let start_Trace = 01JAN2022; 
%let End_Trace = %sysfunc(today(),date9.); 

[PaigeMiller]

You cannot compare a NUMERIC value, such a in tx_date, with a string such as 01JAN2022, which is neither a valid numeric value (it has letters in it). It is not a character string (not surrounded by quotes or double-quotes), and you can't compare a number to character string anyway. So SAS will not perform this comparison.

So, I pose a question to you ... you can compare a number to a number, how can you turn the string 01JAN2022 into a number that will represent 01JAN2022??

[Sandeep77]

I am not sure, I tried using input but that also didn't worked. I added the below step in my code but still does not work.

&Start_trace = input(01JAN2022, yyddmm8.);
&End_trace = input(31DEC2022, yyddmm8.);

[Tom · Accepted solution]

---

@Sandeep77 wrote:

I am not sure, I tried using input but that also didn't worked. I added the below step in my code but still does not work.

&Start_trace = input(01JAN2022, yyddmm8.);
&End_trace = input(31DEC2022, yyddmm8.);

---

You are using the wrong informat there.  YYMMDD informat wants strings that are literally in YearMonthDay order.

There is no YYDDMM informat because nobody uses date strings in YearDayMonth order.  Your strings are something that the DATE informat will recognize.  

But if the string is something the DATE informat will recognize then you can use it to make a date constant.

%let start_trace = "01JAN2022"d ;

Or if you create the macro variable from data don't bother to apply the DATE format to the value so that the macro variable just has a digit string that represents the number of days since 1960 that is actually used to store a date in a SAS variable.

[PaigeMiller]

---

@Sandeep77 wrote:

I am not sure, I tried using input but that also didn't worked. I added the below step in my code but still does not work.

&Start_trace = input(01JAN2022, yyddmm8.);
&End_trace = input(31DEC2022, yyddmm8.);

---

The above code should produce syntax errors, and can't possibly work.

Part of the problem is that you are trying to write macro code, when you have NOT created working SAS code without macros and without macro variables. That should always be the first step. When you have gotten the code to work properly without macros and without macro variables, then you should have much less difficulty turning the code into macro code.

So instead of this part of your PROC SQL

and tx_date >= &Start_trace 

can you write this without a macro variable to compare tx_date to be greater than or equal to something which represents 01JAN2022? You really ought to be able to do this without macro variables and get that to work first.