How to denote date in a readable format?

[tongxu]

I attached a picture of the code. I am trying to create a new variable "INT" according to the range of date in "ORDERING_DATE". The SAS data that this line of data step is reading has "ORDERING_DATE" in mm/dd/yyyy format. What should I change the date (i.e '01MAR2019'D) to, in order for SAS data step to read the date and create the INT variable correctly?

[SASKiwi]

If ORDERING_DATE has been successfully created as a SAS date then your code looks fine to me. Why do you think it is not working correctly?

[tongxu]

All of the INT values are 0. I spot checked, and many should be 1 or 2, because the dates are within the condition boundaries listed. I imported the dataset from excel, and that file has ORDERING_DATE in mm/dd/yyyy format. The code in the picture is someone else's code that I'm taking over, I think he must've converted the excel values for that variable into for example 03MAR2021 format. So that his code is as pictured. I am not planning to convert the excel file into 03MAR2021 format. But this code pictured does not read it correctly. If I can write something that can make it read mm/dd/yyyy, that'd solve the problem.

[Tom]

A FORMAT in SAS is just instructions for how to print the values as text.  It does not change the values.

SAS has two types of variables, fixed length character strings and floating point numbers.  DATE values are stored as numbers.  You can then attach any date format you want to have the date values display in the style you prefer.  But when entering a date literal, like in the code you posted the photograph of, you must use a string that the DATE informat can convert to a date value.  The format attached to the variable does not matter because it does not impact the value the variable has, just how it looks when you print it as text.

If the variable is character instead of numeric you will need to use the INPUT() function to convert the character strings into date values.  If the character strings look like dates in the M-D-Y order you mentioned then use the MMDDYY10. informat to convert them.

If the variable is actually a DATETIME value instead of a DATE value then you will need to use the DATEPART() function to convert the datetime value (number of seconds) Into a date value (number of days) before you can compare it to the date literals in your code.

[svh]

Are you sure that the variable ORDERING_DATE is a SAS date? Or, what is the error message in the log?

[ballardw]

Run Proc Contents on your source data set and show us the results for the variable Ordering_date, as in type and assigned format. One strongly suspects that your variable is not actually date if you are asking with that code.

The actual format applied to a variable has nothing to do with the results of comparisons or calculations though using character values to compare to numeric values has issues with conversion attempts and/or failures.

Does your LOG show any invalid data message such as the following code generates:

data junk;
   /* x is not a date, but a character value*/
   x = '12/15/2021';
   /* y converts the string to a date*/
   y = input(x,mmddyy10.);
   format y mmddyy10.;
   a = x < '15JAN2022'd;
   b = y < '15Jan2022'd;
run;

Which when run has the log show:

84   data junk;
85      /* x is not a date, but a character value*/
86      x = '12/15/2021';
87      /* y converts the string to a date*/
88      y = input(x,mmddyy10.);
89      format y mmddyy10.;
90      a = x < '15JAN2022'd;
91      b = y < '15Jan2022'd;
92   run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      90:8
NOTE: Invalid numeric data, x='12/15/2021' , at line 90 column 8.
x=12/15/2021 y=12/15/2021 a=1 b=1 _ERROR_=1 _N_=1
NOTE: The data set WORK.JUNK has 1 observations and 4 variables.

The note about character conversion and invalid data are both created when X is a character value pretending to be a SAS date value, which is numeric. So X tries to be converted to a number, which will use the BEST12. informat and fails because that informat cannot handle / characters.

[Sajid01]

Hello @tongxu 
Your code appears fine. It is possible that the ordering_date may be a string. In that case it needs to be converted to date format.
I am giving an example in which the date the ordering date is a date number. I have also taken another variable where the date value is stored as a string. In both cases you will get identical results. The code is self explanatory and should help solving the issue. Please let me know if there are questions.

%let d1='01MAR2022'd;
%let d2='10MAR2022'd;
%let d2='20MAR2022'd;
data one;
informat ordering_date_nmbr date9.;
format ordering_date_nmbr date9.;
length ordering_date_str $ 10;
input ordering_date_nmbr  ordering_date_str ;
 if ordering_date_nmbr < &d1. then INT=0;
else if &d1.<= ordering_date_nmbr <= &d2. then INT=1;
else INT=2;
 if input(ordering_date_str,date9.) < &d1. then INT2=0;
else if &d1.<= input(ordering_date_str,date9.)  <= &d2. then INT2=1;
else INT2=2;
datalines;
01MAR2022 01MAR2022
10FEB2022 10FEB2022
10MAR2022 10MAR2022
21MAR2022 21MAR2022
;

The output will be like as follows. The INT and INT2 values are identical.

[Kurt_Bremser]

Please post the complete log of your step by copy/pasting it into a window opened with this button: