Hi, the dates do not work in the where statement. Am i missing something here?
my date field is charectar variable with $10. format.
%let start='01FEB2016'
%let end='01JUN2016'
data want;
set have;
where date between "&start" and "&end";
run;
run;
Hi:
Anytime you use a "readable" date in the DATE9 format, whether you have a character string or not, you MUST use a DATE constant in the syntax for your WHERE or IF or other statements where you want the value treated like a SAS date value.
Assuming that your DATE variable in the dataset is a SAS numeric variable that stores the date as the number of days since Jan 1, 1960, the form you use in your WHERE needs to change.
Consider these 2 versions of a WHERE -- one with a date constant (#2) and one without using a date constant (#1), which is close to your example. The dataset SASHELP.CITIDAY has a variable called DATE which is numeric.
cynthia
The between operator is for numeric variables only. You should never have your date variables in character form. Your real task here is to convert your dates into numeric form 🙂
Hi:
Anytime you use a "readable" date in the DATE9 format, whether you have a character string or not, you MUST use a DATE constant in the syntax for your WHERE or IF or other statements where you want the value treated like a SAS date value.
Assuming that your DATE variable in the dataset is a SAS numeric variable that stores the date as the number of days since Jan 1, 1960, the form you use in your WHERE needs to change.
Consider these 2 versions of a WHERE -- one with a date constant (#2) and one without using a date constant (#1), which is close to your example. The dataset SASHELP.CITIDAY has a variable called DATE which is numeric.
cynthia
Write the date macros as follows:
%let start='01FEB2016'd;
%let end='01JUN2016'd ;
Then your program will work as desired
@thesasuser wrote:
Write the date macros as follows:
%let start='01FEB2016'd;
%let end='01JUN2016'd ;Then your program will work as desired
@thesasuser If the dates are character variables this will not work, if they were numeric this would work..
Dates stored in a DATE9 format will not sort correctly, if you store date as YYYYMMDD they will sort correctly. The better method is to store the date variable as a numeric variable with a date format.
Thanks Cynthia for the detailed explanation.
Also, if i used the format
format date MONYY7., would it sort by month and year? meaning Aug2016 would not go prior to Jan2016 etc..
Do you have any recommendations on using this format?
Thanks
@robertrao wrote:
Thanks Cynthia for the detailed explanation.
Also, if i used the format
format date MONYY7., would it sort by month and year? meaning Aug2016 would not go prior to Jan2016 etc..
Do you have any recommendations on using this format?
Thanks
If you use a SAS date (numeric variable with a date format) then it will sort correctly, unless you explicitly specify otherwise.
If you really have stored your dates as character strings then you will need to convert them actual dates if you want to use them in calculations. The exception might be if you store character dates using year,month,day order (and always use two digits for month adn year) then your can do ordering of the date strings, but not differences or other calculations.
You said that your variable had the $10. format attached to it. I assume you meant that it is a character variable of length 10.
But that does not tell use what style was used to represent the dates in the strings. Assuming it was ddMONyyyy (although if it is then why is it length 10 instead of length 9?) then you could make your WHERE statement like this.
where input(date,date9.) between "&start"d and "&end"d;
You could try using the ANYDTDTE. informat instead of the DATE. informat if you are unsure what format was used to create the date strings.
If you DATE variable is actually created using YYYY-MM-DD style (ie the same as the YYMMDD10. format produces) then perhaps you just need to adjust your macro variables. This would work since date strings in this format when sorted in lexical order will have the same relative order as the dates they represent.
where date between "%sysfunc(putn('&start'd,yymmdd10))" and "%sysfunc(putn('&end'd,yymmdd10))";
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.