Hi:
A review of previous forum posts shows that how SAS handles dates is a frequent question:
http://support.sas.com/forums/thread.jspa?messageID=4454ᅦ
http://support.sas.com/forums/thread.jspa?messageID=6522
http://support.sas.com/forums/thread.jspa?messageID=3597ญ
http://support.sas.com/forums/thread.jspa?messageID=6339ᣃ
http://support.sas.com/forums/thread.jspa?messageID=6180ᠤ
http://support.sas.com/forums/thread.jspa?messageID=6642᧲
http://support.sas.com/forums/thread.jspa?messageID=2121ࡉ
http://support.sas.com/forums/thread.jspa?messageID=6368ᣠ
In order to figure out how to code the BETWEEN in the where clause, you need to know whether the value in your data is a DATE value (the number of days since Jan 1, 1960) or a DATETIME value (the number of seconds since midnight Jan 1, 1960).
Even if you code a date constant in a WHERE, as shown below, SAS is still converting that date to the number of days since Jan 1, 1960. So, run and review the following code [NOT a stored process
😉 Run in a code node in EG.]
[pre]
data birthdays;
infile datalines;
input name $ bdate : date9.;
bdate2 = bdate;
return;
datalines;
alan 15Nov1950
bob 15Nov1980
carl 29Nov1984
dave 01Jan2007
;
run;
proc print data=birthdays;
title 'what does the INTERNAL date look like';
title2 'BDATE is the UNFORMATTED internal storage view; BDATE2 is the FORMATTED, display view';
format bdate2 mmddyy10.;
run;
proc print date=birthdays;
title 'how to get only the person in 1950';
title2 'AND displaying the BDATE2 with a format';
title3 'the INTERNAL value of the Date is still the same';
where bdate between "01Jan1950"d and "31Dec1950"d;
format bdate2 mmddyy10.;
run;
[/pre]
The results from the first PROC PRINT show that:
[pre]
Obs name bdate bdate2
1 alan -3334 11/15/1950
2 bob 7624 11/15/1980
3 carl 9099 11/29/1984
4 dave 17167 01/01/2007
[/pre]
internally, the birth dates are stored as a number of DAYS since Jan 1, 1960. The results from the second proc print show that I have a working WHERE clause, which uses a date constant in the BETWEEN:
[pre]
Obs name bdate bdate2
1 alan -3334 11/15/1950
[/pre]
If I needed to use MACRO variables to build the WHERE clause, then, I would have to do this: [pre]
Working WHERE clause:
where bdate between "01Jan1950"d and "31Dec1950"d;
"macro-ized" WHERE clause
where bdate between "&startd"d and "&stopd"d;
[/pre]
So, where you have table.date -- that's where I have BDATE. Where you have parameter.date1, that's where I have "&startd"d and where you have parameter.date2, that's where I have "&stopd"d -- which still means that &STARTD and &STOPD have to be in the form DDMMMYYYY or DDMMYY -- if I intend to use a date constant in the WHERE.
Although your instincts were right...the form that you chose for your dates: 20070901, 20071231 are not correct -- IF the table.date variable is a SAS date value. First, you just have a character string and you can't compare a character string to an internally stored date value. BUT, you CAN make a text string into a macro variable. So if you had chosen the form 01Sep2007 and 31Dec2007 for your text strings, then you could have turned those text strings into macro variables:
[pre]
%let startd = 01Sep2007;
%let stopd = 31Dec2007;
[/pre]
Since you mentioned in a previous post that you were dealing with datetime variable values, then the correct where clause for a datetime variable would then be:
[pre]
where datepart(table.date1) between "&startd"d and "&stopd"d ;
[/pre]
As you can see in the code below, when you're dealing with DATETIME variable values in the WHERE, the DATEPART function only needs to be applied to the DATETIME variable, NOT to the macro parameter. After all, the macro facility is just doing your typing for you. You can construct the macro variable in any form you want.
Try out this second program in a code node in EG and review the results. My variable BDTIME is a DATETIME variable. The WHERE clause that I have works to select a date range, BUT my macro variable values had to be in the expected form for the D date constant (note that some of my examples use the DT datetime constant for the comparison value) -- but when you use the DATEPART function, SAS is extracting just the date from the datetime value.
cynthia
[pre]
data btime;
infile datalines;
input name $ bdtime : datetime.;
bdtime2 = bdtime;
return;
datalines;
anna 15Nov1950:07:15:00
barb 15Nov1950:10:45:32
cali 15Nov1950:06:33:27
dora 15Nov1950:03:25:59
yora 15Nov1960:04:04:07
zora 15Nov1984:11:56:32
;
run;
proc print data=btime;
title 'what does the INTERNAL datetime look like';
title2 'BDTIME is the UNFORMATTED internal storage view; BDTIME2 is the FORMATTED, display view';
format bdtime2 datetime20.;
run;
proc print date=btime;
title 'how to get only the person born between 2 and 7 on Nov 15th 1950';
title2 'AND displaying the BDTIME2 with a format';
title3 'the INTERNAL value of the DateTime variable is still the same';
where bdtime between '15Nov1950:02:00:00'dt and '15Nov1950:07:00:00'dt;
format bdtime2 datetime20.;
run;
proc print date=btime;
title 'How to get ONLY the people born in 1984';
where year(datepart(bdtime)) = 1984;
format bdtime2 datetime20.;
run;
proc print date=btime;
title 'How to get ONLY the people born on November 15, 1950';
where datepart(bdtime) = "15Nov1950"d;
format bdtime2 datetime20.;
run;
%let startd = 01Jan1960;
%let stopd = 31Dec1984;
proc print date=btime;
title 'How to use macro variables in a BETWEEN with a date time variable';
where datepart(bdtime) between "&startd"d and "&stopd"d;
format bdtime2 datetime20.;
run;
[/pre]