Your SAS programs, embedded in web apps and elsewhere

Proper format for comparing parameter data to table data

Reply
N/A
Posts: 0

Proper format for comparing parameter data to table data

In a stored process I'm making, I have a parameter that asks for a year to be entered and another parameter that asks for a semester to be entered (ex: 2007 Fall. I then convert the semester to the text equivalent of a month and date range (ex: Fall semester will be 0901 for first date, 1231 for end date). I then can concatenate these years and dates, in text format, to these two valuses: 20070901, 20071231.

After that, I need to compare these values, in Proc Sql, to table date values. There is where I'm having difficulty. I want to set up a sql statement similar to this: WHERE table.date IS BETWEEN parameter.date1 and parameter.date2. However, my code does not recognize date1 or date2.

I'm thinking I shopuld use something like the DATEPART function, so that my code would end up like WHERE DATEPART table.date BETWEEN DATEPART(parameter.date1) and (DATEPART(parameter.date2). Please notice I said "....like...." I know this is not the exact code, however for my question it is close enough.

I am NOT using macros this time around, although I feel more comfortable in macros.

Thak you in advance to any who reply.....looking forward to hearing from you.
SAS Super FREQ
Posts: 8,868

Re: Proper format for comparing parameter data to table data

Posted in reply to deleted_user
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]
N/A
Posts: 0

Re: Proper format for comparing parameter data to table data

Posted in reply to Cynthia_sas
Thanks Cynthia! I still marvel at the length and detail of your replies and knowledge. I'll be studying what you responded with. Thanks again!
Contributor
Posts: 49

Re: Proper format for comparing parameter data to table data

Posted in reply to deleted_user
SASMan,

I think the key thing to remember with dates is that SAS has to be told that the variable is a date. It won't convert a text variable (such as 20070926) to a SAS date unless you tell it to (i.e. using an INPUT function and an informat).

As usual, Cynthia has provided a detailed and well thought-out reply .... again! Anyone would think she works for SAS Smiley Happy.

Lawrence
N/A
Posts: 0

Re: Proper format for comparing parameter data to table data

Posted in reply to LawrenceHW
Right!! and yes, Cynthia does provide wonderful replies with detailed information. You mean she doesn't work for SAS ??????
SAS Super FREQ
Posts: 8,868

Re: Proper format for comparing parameter data to table data

Posted in reply to deleted_user
I believe that Lawrence was having a joke on me. I DO work for SAS (for over 11 years) and have been using SAS for "mumblety-mumble" (in the vicinity of 25 )years.

cynthia
N/A
Posts: 0

Re: Proper format for comparing parameter data to table data

Posted in reply to Cynthia_sas
Which I had figured already!! Getting back to the date issue, I'm going insane trying to work on a multiple date comparison in my PROC SQL, using dates coded into a macro as follows..... this is for a college, so they use a group of 4 semester dates (Sem 1 is 09Sep2004 to 12Dec2004, Sem 2 is 01Jan2005 to 31May2005 and so on.) I have a parameter screen where the user can pick one or all semesters. In my macro, I have code set up to build a string of date comparators:

%if %superq(termpick) = Fall %then %do;
%let period1 = 01Sep&previousyearch ;
%let period2 = 31Dec&previousyearch ;
%let termcls = AND Datepart(Current_Hire_Date) BETWEEN (input((UPCASE("&period1")),date9.)) and (input((UPCASE("&period2")),date9.));
%end;

If multiple semesters are chosen, I then have code for concatenating the date comparators:

%if %superq(termpick0) ne
%then %do i=2 %to %superq(termpick0);
%if %superq(termpick&i) = Fall %then %do;
%let period1 = 01Sep&previousyearch ;
%let period2 = 31Dec&previousyearch ;
%let termcheck = Datepart(Current_Hire_Date) BETWEEN (input ((UPCASE"&period1")),date9.)) and (input((UPCASE("&period2")),date9.));
%end;

followed by

%let termcls = &termcls OR %superq(termcheck);

This does not work....I get the following error:

177 AND Datepart(Current_Hire_Date) BETWEEN (input(UPCASE("01Sep2004"),date9.)) and (input(UPCASE("31Dec2004"),date9.) OR Datepart(Current_Hire_Date) BETWEEN
177 ! (input(UPCASE("01Jan2005"),date9.) and (input(UPCASE("31May2005"),date9.)
_ _
22 22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, ), *, +, -, ALL, BTRIM, CALCULATED, CASE,
The SAS System

DISTINCT, EXISTS, INPUT, LOWER, NOT, PUT, SUBSTRING, TRANSLATE, UNIQUE, UPPER, USER, ^, ~.

__ __
200 200
ERROR 200-322: The symbol is not recognized and will be ignored.

I don't know if you can read the code and message I enclosed with my note, but am mainly curious about how to make a concatenated date comparison.

Thanks for all the help you have given me....it's been wonderful!!
SAS Super FREQ
Posts: 8,868

Re: Proper format for comparing parameter data to table data

Posted in reply to deleted_user
Hi:
I'm not sure I understand exactly what you want here -- BUT if you can MAKE &PERIOD1 and &PERIOD2 values in the DATE9 format (which it looks like you're very close to), then you can avoid this convoluted comparison:
[pre]
BETWEEN (input((UPCASE("&period1")),date9.)) ...
[/pre]
and use &PERIOD1 and &PERIOD2 as DATE constants, like this. For example, Let's assume that &PERIOD1 resolves to 01SEP2007 and &PERIOD2 resolves to 12DEC2007
[pre]
%let termcls = AND Datepart(Current_Hire_Date) BETWEEN "&period1"d and "&period2"d;
[/pre]
would resolve to:
[pre]
%let termcls = AND Datepart(Current_Hire_Date) BETWEEN "01SEP2007"d and "12DEC2007"d;
[/pre]

There should be no need for you to explicitly uppercase the date9 version of the macro variable value, because all SAS is doing is using the macro variable value to type a date for you. Internally, the date will be converted to a NUMBER.

However, this code shows &TERMCLS being built using the macro variable to provide a value for the date constant and this should be sufficient for your comparison. Note the use of %upcase to upcase the date value -- regular UPCASE is a data step function that may not work in all macro situations -- so %UPCASE macro function comes in handy. (Note that I changed your parentheses in &TERMCLS)
[pre]
%global termpick previousyrch period1 period2 termcls uperiod1 uperiod2 utermcls;

%let termpick = Fall;
%let previousyrch = 2007;
%let period1 = 01Sep&previousyrch;
%let uperiod1 = %upcase(01Sep&previousyrch);
%let period2 = 31Dec&previousyrch;
%let uperiod2 = %upcase(31Dec&previousyrch);
%let termcls = AND Datepart(Current_Hire_Date) BETWEEN "&period1"d and "&period2"d;
%let utermcls = AND Datepart(Current_Hire_Date) BETWEEN "&uperiod1"d and "&uperiod2"d;
[/pre]
Then, when you run this code in a code node (not as a stored process), here's what you SHOULD see in the SAS Log:
[pre]
95
96 %global termpick previousyrch period1 period2 termcls uperiod1 uperiod2 utermcls;
97
98 %let termpick = Fall;
99 %let previousyrch = 2007;
100 %let period1 = 01Sep&previousyrch;
101 %let uperiod1 = %upcase(01Sep&previousyrch);
102 %let period2 = 31Dec&previousyrch;
103 %let uperiod2 = %upcase(31Dec&previousyrch);
104 %let termcls = AND Datepart(Current_Hire_Date) BETWEEN "&period1"d and "&period2"d;
105 %let utermcls = AND Datepart(Current_Hire_Date) BETWEEN "&uperiod1"d and "&uperiod2"d;
106
107 %put termpick= &termpick;
termpick= Fall
108 %put previousyrch = &previousyrch;
previousyrch = 2007
109 %put period1= &period1;
period1= 01Sep2007
110 %put uperiod1 = &uperiod1;
uperiod1 = 01SEP2007
111 %put period2= &period2;
period2= 31Dec2007
112 %put uperiod2= &uperiod2;
uperiod2= 31DEC2007
113 %put termcls = &termcls;
termcls = AND Datepart(Current_Hire_Date) BETWEEN "01Sep2007"d and "31Dec2007"d
114 %put utermcls = &utermcls;
utermcls = AND Datepart(Current_Hire_Date) BETWEEN "01SEP2007"d and "31DEC2007"d
[/pre]

I believe that part of your confusion is that it looks like you want to treat the macro variable values as though there are data set variables -- but that's not the best approach. For example, when you do this: [pre]
%let termcls = AND Datepart(Current_Hire_Date) BETWEEN (input((UPCASE("&period1")),date9.)) and (input((UPCASE("&period2")),date9.));
[/pre]

Your use of the DATEPART function is good -- because if Current_Hire_Date is a date/time variable, then you must use the DATEPART function to extract only the date portion of the value. But then, you use the INPUT and UPCASE functions on &PERIOD1 and &PERIOD2 - but you don't really need to do this, because they are NOT data step variables. If you did have a character data -- INSIDE your DATA -- then you would need to use the INPUT function to turn your character string into a SAS date value. But, macro variables are just TEXT strings that are being substituted into your code. If you were creating a new variables from &PERIOD1, you might do it like this:
[pre]
proc sql ;
create table work.testdate as
select name,
"&period1"d as numdate,
"&period1" as chardate,
input("&period1",date9.) as sillydate
from sashelp.class;

select * , input(chardate,date9.) as sillydate2
from work.testdate;
quit;
[/pre]

Note that none of those references to &PERIOD1 need to be upcased for them to work correctly -- BUT -- I am using the macro variables to create new data set variables -- and in the above case, the date constant technique "&PERIOD1"d works just as well as the INPUT technique for DATA SET variable creation.

When you are using a WHERE clause, however, you are not referencing a data set variable you are references a Macro variable that is taking advantage of the fact that one time when you run the WHERE clause, you'll have one set of dates and the next time, when you run the stored process with the WHERE clause, you'll have a different set of dates.

The Macro facility is ONLY acting like a typewriter and putting the values into the code in the right place before the code is sent to the compiler. Because the Macro facility is just acting like a big typewriter, then it is up to you to use the macro variable in the right way so the eventually-compiled code is correct. I believe that the simple date constant technique will work much better for you.

Good luck,
cynthia
N/A
Posts: 0

Re: Proper format for comparing parameter data to table data

Posted in reply to Cynthia_sas
Cynthia, thanks many times for your explanations..... I'll spend some time reading what you replied with, and see how your explanations / advice fit into my project. I'm also pushing on my end for many more books to read and for attending some of SAS's classes.... this piecemeal approach is eating my lunch, if you know what I mean. We have plenty ofmanuals from instructor-led classes where I work, but those only lead by the nose.

Thanks again, many times over!
N/A
Posts: 0

Re: Proper format for comparing parameter data to table data

Posted in reply to Cynthia_sas
Hi, again! I tried using your suggestion, but it didn't work for me. What I'm trying s to make a ""combination BETWEEN phrase, something like:

If A between B and C or A between D and E or A between F and G or A between H and I

but with the date items, here is what I received beack:

AND Datepart(Current_Hire_Date) BETWEEN "01SEP2004"d and "31DEC2004"d OR Datepart(Current_Hire_Date) BETWEEN "01JAN2005"d and "31MAY2005"d

And here is the error I received:

Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, +, -, BTRIM, CALCULATED, CASE, INPUT, LOWER, PUT, SUBSTRING, TRANSLATE, UPPER, USER.

The error message was pointing to the quote symbol (") immediately before 01JAN2005.

Am I using your suggestion incorrectly?
SAS Super FREQ
Posts: 8,868

Re: Proper format for comparing parameter data to table data

Posted in reply to deleted_user
Hi,
All I can conclude is that there's something going awry with how you're building your WHERE clause. This code, run in a code node, with hard-coded values for &STARTD1, &STOPD1, &STARTD2 and &STOPD2 does NOT get any error messages when I execute it:
[pre]
** 1a) make some data;
data btime;
infile datalines;
input name $ bdtime : datetime.;
bdtime2 = bdtime;
format bdtime2 datetime20.;
label bdtime = 'Unformatted Date Time Value'
bdtime2 = 'Formatted with DATETIME20';
return;
datalines;
anna 15Nov1941:07:15:00
barb 15Nov1942:10:45:32
cali 15Nov1945:06:33:27
dora 15Nov1950:03:25:59
yora 15Nov1960:04:04:07
zora 15Nov1984:11:56:32
;
run;

** 1b) make some macro variables;
%let startd1 = 01Jan1960;
%let stopd1 = 31Dec1984;
%let startd2 = 01Jan1941;
%let stopd2 = 31Dec1943;

** 1c) test complex BETWEEN;
title 'SQL complex where';
proc sql;
select *
from btime
where datepart(bdtime) between "&startd1"d and "&stopd1"d
OR
datepart(bdtime) between "&startd2"d and "&stopd2"d ;
quit;
[/pre]

Now that the above code executes for me without ANY problems, I can turn it into a macro program. As a SAS Macro program, again, if I execute the code from within a code node, I do not get any error messages.
[pre]
** 2a) Before testing macro program, make some macro variables;
** that will be like what is sent from the client application;
** or built by the user;
%let startd1 = 01Jan1960 ;
%let stopd1 = 31Dec1984 ;
%let startd2 = 01Jan1941 ;
%let stopd2 = 31Dec1943 ;
%let startd3 = 01Jan1950 ;
%let stopd3 = 31Dec1953 ;
%let startd0 = 3 ;
%let stopd0 = 3 ;

** 2b) macro program to build where clause;
%macro mkwhere;
%global wherecls startd0 startd1 stopd1 startd2 stopd2 startd3 stopd3 ;
%do i = 1 %to &startd0;
%put -----> startd&i= &&startd&i;
%put -----> stopd&i= &&stopd&i;
%if &i = 1 %then %let wherecls = WHERE (datepart(bdtime) BETWEEN "&&startd&i"d and "&&stopd&i"d) ;
%else %let wherecls = &wherecls. OR (datepart(bdtime) BETWEEN "&&startd&i"d and "&&stopd&i"d) ;
%end;
%let wherecls = &wherecls %str(Smiley Wink;

%mend mkwhere;

** 2c) invoke macro program using macro variables set in the above %let stmts;
** that "mimic" what the stored process server will return;
** note that I am hard coding only 3 selections;
** and NOT coding for the 0 or 1 selection situation;
** NEED to build where clause separate from PROC SQL for this example;
options mprint symbolgen mlogic;

%mkwhere;
%put -----> What is constructed WHERE clause;
%put -----> %unquote(&wherecls);

** 2d) Use the where clause build in 2c;
title 'SQL complex where with clause built by macro';
proc sql;
select *
from btime
%unquote(&wherecls);
quit;

[/pre]

Since I built the macro variable &WHERECLS inside the %MKWHERE macro program, I wanted to be sure to strip any leading and trailing blanks and I also wanted to make sure that the WHERE clause that I built was syntactically correct, so the final %LET adds the semi-colon using the %STR function. Generally, the MACRO facility puts special unprintable characters around any macro references that are generated using "quoting" functions, like
%QUOTE, %STR, %SUPERQ, etc -- so when I reference &WHERECLS, I use the %UNQUOTE function just to be sure that those special unprintable characters are removed.

But the point is that I cannot duplicate your problem or your error message. There's nothing about running the above code as a stored process that's any different -- if the macro program works -outside- of a stored process, it should work -inside- a stored process. The key is to make a WHERE clause with a macro program that works OUTSIDE a stored process.

My only other suggestion is the one I've made before...have a working program like that shown in #1. Turn that working program into a macro program, as shown in #2. Then when you turn the #2 program into a stored process, you should not have any issues. The way I would turn MY #2 program into a stored process is like this:
[pre]
*ProcessBody;
%global wherecls startd0 startd1 stopd1 startd2 stopd2 startd3 stopd3 ;

%macro mkwhere;
%do i = 1 %to &startd0;
%if &i = 1 %then %let wherecls = WHERE (datepart(bdtime) BETWEEN "&&startd&i"d and "&&stopd&i"d) ;
%else %let wherecls = &wherecls. OR (datepart(bdtime) BETWEEN "&&startd&i"d and "&&stopd&i"d) ;
%end;
%let wherecls = &wherecls %str(Smiley Wink;
%mend mkwhere;

%mkwhere;

%stpbegin;
title 'SQL complex where with clause built by macro';
proc sql;
select *
from btime
%unquote(&wherecls);
quit;
%stpend;

[/pre]

You really are at a point where you need to contact Tech Support for help. They can help you figure out the best approach to what you want to do and help you with your macro logic and coding. My only other observation is that you said you wanted to generate this:
[pre]
If
A between B and C or A between D and E or A between F and G or A between H and I [/pre]
by which I assume you meant a [pre]
WHERE
A between B and C or A between D and E or A between F and G or A between H and I
[/pre]
but your generated code shows an AND in front of the BETWEEN clauses,
[pre]
AND
Datepart(Current_Hire_Date) BETWEEN "01SEP2004"d and "31DEC2004"d OR Datepart(Current_Hire_Date) BETWEEN "01JAN2005"d and "31MAY2005"d
[/pre]
which causes me to wonder how you executed this successfully outside of a stored process with the stray AND there. Should the AND be an IF or a WHERE?

cynthia
N/A
Posts: 0

Re: Proper format for comparing parameter data to table data

Posted in reply to Cynthia_sas
Hi!

To answer your question at the end of your reply, "but your generated code shows an AND in front of the BETWEEN clauses,

AND Datepart(Current_Hire_Date) BETWEEN "01SEP2004"d and "31DEC2004"d OR Datepart(Current_Hire_Date) BETWEEN "01JAN2005"d and "31MAY2005"d

which causes me to wonder how you executed this successfully outside of a stored process with the stray AND there. Should the AND be an IF or a WHERE? "

That was because I had some other condition checking in my WHERE statements.... i.e., WHERE x=y AND johnny passed tests AND tuitiion owed is null ...... leading into my date checking code.....s othat's why I had the AND where you noticed it.

Perhaps I'm just too accustomed to working with dates in MS Access and VB, but it sure seems that SAS date logic is a headache.

Thanks again for replying and for taking your time to help me. It is appreciated.
SAS Super FREQ
Posts: 8,868

Re: Proper format for comparing parameter data to table data

Posted in reply to deleted_user
Hi:
I guess it's a matter of opinion. I'm used to the way SAS handles dates AND I've been doing macro programming for over 20 years -- so using the two together is NOT confusing for me. The simple form of the date reference (date constant) should work for you (with a macro variable and a date constant) in the BETWEEN.

The use of the date constant in the WHERE clause requires 2 things:
1) that my date (or macro variable value) be in a certain form DDMMMYYYY or DDMMMYY and
2) that my date be enclosed in quotes followed by a d [pre]
"01JAN1940"d or "01Jan1940"d or "01jan1940"d or "01jAn1940"d [/pre]
would all be treated the same way. When you use a date constant, you are instructing SAS to take the readable date and turn it into a number for purposes of the comparison. It doesn't make any difference if what's in the quotes is something you type or something that comes from a macro variable. They would be treated the same once the macro variable reference was resolved:
[pre]
%let bstart = 01Jan1940;
and then my usage of
"&bstart"d
resolves to
"01JAN1940"d
[/pre]

To make a good comparison to this date constant in the WHERE clause, I ALSO have to know how my date is stored in the data set or table:
1) IF the date is the number of days since Jan 1, 1960 (a simple date value), then my comparison for a between could be: [pre]
WHERE birthday between "01JAN1940"d and "31DEC1960"d; [/pre]
or
[pre]
%let bstart = 01JAN1940;
%let bstop = 31DEC1960;
WHERE birthday between "&bstart"d and "&bstop"d ;[/pre]

But
2) IF the date in my table is a date/time value (more complex date value -- number of SECONDS since midnight Jan 1, 1960), then I have to use the DATEPART function on my DATA SET variable (not on my macro variable):
[pre]
WHERE datepart(birthday) between "01JAN1940"d and "31DEC1960"d
WHERE datepart(birthday) between "&bstart"d and "&bstop"d [/pre]

The macro variable in the comparison works the same way -- it is the knowledge of how SAS date values need to be handled that changes the code in my WHERE clause.

Since you are stringing your BETWEENs with an AND, then I recommend the use of added parentheses to make sure that your condition executes correctly -- in addition to everything else. This WHERE clause uses character variables, numeric variables and date constants with AND and OR conditions:
[pre]
WHERE name="Fred" AND
howold gt 40 AND
((datepart(birthday) BETWEEN "01JAN1940"d AND "31DEC1960"d)
OR
(datepart(hiredate) BETWEEN "01JAN1995"d AND "31DEC2006"d));
[/pre]

Once I code the above WHERE clause with hard-coded values for a test case and it is selecting the right group of people for a given data set, then I can macro-ize this WHERE clause. The key is having a WHERE clause that I know works and then adding the macro variables afterward:

[pre]
%let want=Fred;
%let agelim = 40;
%let bstart = 01JAN1940;
%let bstop = 31DEC1960;
%let hstart = 01JAN1995;
%let hstop = 31DEC2006;

WHERE name="&want" AND
howold gt &agelim AND
((datepart(birthday) BETWEEN "&bstart"d AND "&bstop"d)
OR
(datepart(hiredate) BETWEEN "&hstart"d AND "&hstop"d));
[/pre]

To "macro-ize" the WHERE clause, I just have to type the macro variable name in the appropriate place. For &WANT, &BSTART, &BSTOP, &HSTART and &HSTOP -- the value goes into quotes because that's the syntax required by the WHERE clause. for &AGELIM, my data set variable HOWOLD is numeric, so for purposes of the comparison, &AGELIM needs to be treated like a number.

To all of the above, you're adding some fairly complex macro coding -- putting everything into a macro program and using macro %DO loops -- which is fairly advanced stuff. SAS Macro Facility doesn't care whether you're making macro variables that will be treated as numbers, text strings or dates. The Macro Facility just keeps taking in macro variable values and every place in your code that SAS detects a reference to a macro variable, then some text substitution or typing happens where SAS inserts the macro variable value into the generated code before the generated code goes to the compiler.

Anyway, for your headache, whether it's date caused or macro caused, the only cure that I know is to take it slow and try to understand each step along the way. You're at a very good stage in the learning curve -- right now, you're practicing what you're learning -- and even though it's hard and frustrating -- the end result will be that eventually, you'll know SAS as well as you know MS Access and VB.

cynthia
N/A
Posts: 0

Re: Proper format for comparing parameter data to table data

Posted in reply to Cynthia_sas
Hi, Cynthia,

Thanks for the detailed reply, and by the way, I GOT IT WORKING!!!! :-)

I appreciate how much you helped me and how much you have helped in previous postings.
SAS Super FREQ
Posts: 8,868

Re: Proper format for comparing parameter data to table data

Posted in reply to deleted_user
Wonderful news! Congrats!
cynthia
Ask a Question
Discussion stats
  • 14 replies
  • 495 views
  • 0 likes
  • 3 in conversation