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?
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?
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.
Are you sure that the variable ORDERING_DATE is a SAS date? Or, what is the error message in the log?
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.
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.
Please post the complete log of your step by copy/pasting it into a window opened with this button:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.