Hi:
Thanks so much for mentioning this "ReportTime is still a character variable so SAS will probably not perform the type of comparison that you are looking for".
I was hoping someone would also ask or point out that IF ReportTime is a character variable, then the WHERE clause would expect that ReportTime was being compared to a quoted character string. Since we were never shown the values for &MTDfromdate and &MTDTodate, that led me to wonder whether the OP actually understood the difference between using character strings in comparisons and using numeric values in comparisons.
Also, I sort of wondered why the BETWEEN operator wasn't being used since it seems perfect for this.
But back to the &MTDfromdate and &MTDtodate issue. At some point, I expected to hear of an error message like this:
[pre]
ERROR: Expression using greater than or equal (>=) has components that are of different data types.
[/pre]
...or to discover that the WHERE clause was not selecting the correct record set.
Consider this data:
[pre]
data bday;
infile datalines dlm=' ';
input name $ bday : mmddyy10. charbday $;
unfmtbday = bday;
format bday mmddyy10.;
return;
datalines;
alan 11/15/1950 15Nov50
bob 05/09/1955 09May55
cathy 11/19/1966 19Nov66
dave 11/29/1984 29Nov84
eliza 05/24/1936 24May36
fran 11/15/1950 15Nov50
george 11/15/1950 15Nov50
;
run;
proc print data=bday;
title 'What does data look like';
run;
[/pre]
Note that there is a character string that represents the birthday and 2 numeric variables that represent birthday: BDAY is the numeric birthday value formatted with the MMDDYY10. format; UNFMTBDAY is the numeric birthday value which is not formatted in order to see the internally stored number; and CHARBDAY is a character string that represents the birthday.
[pre]
What does data look like
Obs name bday charbday unfmtbday
1 alan 11/15/1950 15Nov50 -3334
2 bob 05/09/1955 09May55 -1698
3 cathy 11/19/1966 19Nov66 2514
4 dave 11/29/1984 29Nov84 9099
5 eliza 05/24/1936 24May36 -8622
6 fran 11/15/1950 15Nov50 -3334
7 george 11/15/1950 15Nov50 -3334
[/pre]
Now, given that data the program below uses some macro variables to select observations in PROC SQL. Here are the macro variables:
[pre]
%let usenum = -3334;
%let useconstant = 15Nov50;
%let startchar = 15Nov50;
%let endchar = 31May55;
[/pre]
Next, the program has 7 different PROC SQL steps, each with a different WHERE clause, as follows:
[pre]
a) where bday = "&useconstant"d;
b) where bday = &usenum;
c1) where bday between "&startchar"d and "&endchar"d;
c2) where bday GE "&startchar"d and bday LT "&endchar"d;
d) where charbday = "15Nov50";
e) where charbday GE "&startchar" and charbday LT "&endchar";
f) where bday GE "&startchar" and charbday LT "&endchar";
[/pre]
A, B, C1, and C2 will all work becasue they are correctly comparing the numeric value for BDAY to a correctly specified date constant or number. D will work because the character variable CHARBDAY is being compared to only 1 character value for selection. E will work but will not select the correct rows because the character value for CHARBDAY is being tested as a CHARACTER string (not against real numeric values that represent dates). F will not work because the numeric value for BDAY is being compared to 2 character strings and SQL will not allow you to have 2 different data types in a comparison.
For comparison purposes, the output from C1, C2 and E are shown below (with **** to divide the 3 outputs):
[pre]
c1) Use BETWEEN with macro var for comparison
WHERE BDAY BETWEEN -3334 and -1676 (as numeric)
name bday charbday unfmtbday
-----------------------------------------
alan 11/15/1950 15Nov50 -3334
bob 05/09/1955 09May55 -1698
fran 11/15/1950 15Nov50 -3334
george 11/15/1950 15Nov50 -3334
*******************************************
c2) Use other operators with macro var for comparison
where bday GE -3334 and bday LT -1676 (as numeric)
name bday charbday unfmtbday
-----------------------------------------
alan 11/15/1950 15Nov50 -3334
bob 05/09/1955 09May55 -1698
fran 11/15/1950 15Nov50 -3334
george 11/15/1950 15Nov50 -3334
*******************************************
e) Try Character variables in the WHERE with diff condition
where charbday GE 15Nov50 and charbday LT 31May55 (as character)
This will NOT work
name bday charbday unfmtbday
-----------------------------------------
alan 11/15/1950 15Nov50 -3334
cathy 11/19/1966 19Nov66 2514
dave 11/29/1984 29Nov84 9099
eliza 05/24/1936 24May36 -8622
fran 11/15/1950 15Nov50 -3334
george 11/15/1950 15Nov50 -3334
[/pre]
I used date values and data constants to simplify the program. However, SAS date/time values are still just internally stored numbers -- they're just a lot bigger, representing the number of seconds since midnight on Jan 1, 1960 instead of the number of days from Jan 1, 1960. Since we never saw the whole program or the macro variable values, I can't say for sure whether the WHERE clause in PROC SQL worked correctly. We still don't know for sure that ReportTime actually -is- a character variable.
Every once in a while, it helps to make sure that we are all on the same wavelength as far as macro variables and date (or date/time) values in comparisons. The SQL steps are included below.
cynthia
[pre]
%let usenum = -3334;
%let useconstant = 15Nov50;
%let startchar = 15Nov50;
%let endchar = 31May55;
title 'a) use macro variable with date constant';
proc sql;
select *
from work.bday
where bday = "&useconstant"d;
quit;
title 'b) use macro variable with date number';
proc sql;
select *
from work.bday
where bday = &usenum;
quit;
title 'c1) Use BETWEEN with macro var for comparison';
title2 "WHERE BDAY BETWEEN %sysfunc(inputn(&startchar,date9.)) and %sysfunc(inputn(&endchar,date9.)) (as numeric)";
proc sql;
select *
from work.bday
where bday between "&startchar"d and "&endchar"d;
quit;
title 'c2) Use other operators with macro var for comparison';
title2 "where bday GE %sysfunc(inputn(&startchar,date9.)) and bday LT %sysfunc(inputn(&endchar,date9.)) (as numeric)";
proc sql;
select *
from work.bday
where bday GE "&startchar"d and bday LT "&endchar"d;
quit;
title 'd) Try Simple EQ in WHERE';
title2 'This will appear to work';
proc sql;
select *
from work.bday
where charbday = "15Nov50";
quit;
title 'e) Try Character variables in the WHERE with diff condition';
title2 "where charbday GE &startchar and charbday LT &endchar (as character)";
title3 "This will NOT work";
proc sql;
select *
from work.bday
where charbday GE "&startchar" and charbday LT "&endchar";
quit;
title 'f) Try comparing numeric bday to character string';
title2 "where bday GE &startchar and charbday LT &endchar (as mixed type)";
title3 "This will NOT work";
proc sql;
select *
from work.bday
where bday GE "&startchar" and charbday LT "&endchar";
quit;
title;
[/pre]
