DATA Step, Macro, Functions and more

SAS Date

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

SAS Date

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;


Accepted Solutions
Solution
‎04-15-2017 06:46 PM
SAS Super FREQ
Posts: 8,868

Re: SAS Date

[ Edited ]
Posted in reply to robertrao

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


All Replies
PROC Star
Posts: 763

Re: SAS Date

Posted in reply to robertrao

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 Smiley Happy

Solution
‎04-15-2017 06:46 PM
SAS Super FREQ
Posts: 8,868

Re: SAS Date

[ Edited ]
Posted in reply to robertrao

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

Contributor
Posts: 21

Re: SAS Date

Posted in reply to Cynthia_sas

Write the date macros as follows:

 

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

Then your program will work as desired

 

 

Super User
Posts: 19,851

Re: SAS Date

Posted in reply to thesasuser

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. 

Super Contributor
Posts: 1,041

Re: SAS Date

Posted in reply to Cynthia_sas

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

Super User
Posts: 19,851

Re: SAS Date

Posted in reply to robertrao

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.

Super User
Super User
Posts: 7,074

Re: SAS Date

Posted in reply to robertrao

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))";
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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