BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

use_date_constant_macro.png

View solution in original post

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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 🙂

Cynthia_sas
SAS Super FREQ

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

use_date_constant_macro.png

thesasuser
Pyrite | Level 9

Write the date macros as follows:

 

%let start='01FEB2016'd;
%let end='01JUN2016'd ;

Then your program will work as desired

 

 

Reeza
Super User

@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. 

robertrao
Quartz | Level 8

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

Reeza
Super User

@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.

Tom
Super User Tom
Super User

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))";

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1790 views
  • 4 likes
  • 6 in conversation