Prev: how to pick the last 65000 rows
Next: Updated comparison table for SAS-SPSS Add-ons and R Functions
From: Joe Matise on 13 Jan 2010 14:29
First off, I believe SYSFUNC arguments are intended to be unquoted (no ' "
). 'month' should be month (no quotes).

Secondly, "&EndDate"d won't work in the SYSFUNC, as far as I can tell.
You'll need to either pass it as a date value, or inside the sysfunc input
it into a date value.

Finally, you are recursively referenceing EndDate in the %let statement -
probably not good idea. It doesn't do any harm, but it makes it difficult
to see the errors.

This works, for example:

%macro test( StartDate, EndDate);
%let EndDate=%sysfunc(intnx('month',&EndDate,1));
%let StartDate=01&StartDate.;
%let where="&StartDate."d<=model<="&EndDate."d;
%mend(test);

%test( jan09, 17198);

-Joe



On Wed, Jan 13, 2010 at 12:53 PM, Masoud Pajoh <mpajoh(a)odot.org> wrote:

> All:
> The following is a fragment of a much larger macro.
>
> %macro test( StartDate, EndDate);
> %let EndDate=%sysfunc(intnx('month',"&EndDate."d,1));
> %let StartDate=01&StartDate.;
> %let where="&StartDate."d<=model<="&EndDate."d;
> %mend(test);
>
> %test( jan09, dec09);
>
> WHERE should resolve to "01jan09"d<=model<="01jan10"d
> But, I get:
>
> WARNING: An argument to the function INTNX referenced by the %SYSFUNC or
> %QSYSFUNC macro function is out of range.
>
> What is wrong?
>
> Thanks,
>
> Masoud
>
From: "Data _null_;" on 13 Jan 2010 14:41
You can use date literials in %SYSFUNC(INTCK they just have to be
correct date7 or date9 format. In this case we need the day part.

0 %macro test( StartDate, EndDate);
11 %let EndDate=%sysfunc(intnx(month,"01&EndDate."d,1),date9);
12 %let StartDate=01&StartDate.;
13 %let where="&StartDate."d<=model<="&EndDate."d;
14 %put WHERE=&where;
15 %mend;
16
17 %test( jan09, dec09);
WHERE="01jan09"d<=model<="01JAN2010"d


On 1/13/10, Joe Matise <snoopy369(a)gmail.com> wrote:
> First off, I believe SYSFUNC arguments are intended to be unquoted (no ' "
> ). 'month' should be month (no quotes).
>
> Secondly, "&EndDate"d won't work in the SYSFUNC, as far as I can tell.
> You'll need to either pass it as a date value, or inside the sysfunc input
> it into a date value.
>
> Finally, you are recursively referenceing EndDate in the %let statement -
> probably not good idea. It doesn't do any harm, but it makes it difficult
> to see the errors.
>
> This works, for example:
>
> %macro test( StartDate, EndDate);
> %let EndDate=%sysfunc(intnx('month',&EndDate,1));
> %let StartDate=01&StartDate.;
> %let where="&StartDate."d<=model<="&EndDate."d;
> %mend(test);
>
> %test( jan09, 17198);
>
> -Joe
>
>
>
> On Wed, Jan 13, 2010 at 12:53 PM, Masoud Pajoh <mpajoh(a)odot.org> wrote:
>
> > All:
> > The following is a fragment of a much larger macro.
> >
> > %macro test( StartDate, EndDate);
> > %let EndDate=%sysfunc(intnx('month',"&EndDate."d,1));
> > %let StartDate=01&StartDate.;
> > %let where="&StartDate."d<=model<="&EndDate."d;
> > %mend(test);
> >
> > %test( jan09, dec09);
> >
> > WHERE should resolve to "01jan09"d<=model<="01jan10"d
> > But, I get:
> >
> > WARNING: An argument to the function INTNX referenced by the %SYSFUNC or
> > %QSYSFUNC macro function is out of range.
> >
> > What is wrong?
> >
> > Thanks,
> >
> > Masoud
> >
>
From: Chang Chung on 13 Jan 2010 14:44
On Wed, 13 Jan 2010 12:53:46 -0600, Masoud Pajoh <mpajoh(a)ODOT.ORG> wrote:

>All:
>The following is a fragment of a much larger macro.
>
>%macro test( StartDate, EndDate);
> %let EndDate=%sysfunc(intnx('month',"&EndDate."d,1));
> %let StartDate=01&StartDate.;
> %let where="&StartDate."d<=model<="&EndDate."d;
>%mend(test);
>
>%test( jan09, dec09);
>
>WHERE should resolve to "01jan09"d<=model<="01jan10"d
>But, I get:
>
>WARNING: An argument to the function INTNX referenced by the %SYSFUNC or
>%QSYSFUNC macro function is out of range.
>
>What is wrong?

Hi, Masoud,

A few things are wrong. Below is one possible revision. Fixed are:
(1) parameter names are changed, because your macro expects months (in mmmyy
format), not dates
(2) 'month' is unquoted, since in macro no quotes are needed -- everything's
text!
(3) 01 is prepended to make the end month a date
(4) date9 format is added to %sysfunc() in order to get the date literal not
the date value (which is a number) back
(5) macro variable where is not used. instead the macro returns a where
expression, instead.
(6) the returned where expression includes the starting date, but *excludes*
the ending date, because the ending date is one day *after* the end of the
given end month.
(7) %mend statement is fixed.

The macro still is not up to any production job, because it does not check
the arguments for validity (both should be mmmyy, end should not come before
start, ...) and handles them if they are not. You have to clearly document
what kind of input it expects. Using the two digit year means that the
output can be changed according to the yearcutoff system option. Having the
hardcoded string like "model" (in the middle of the where expression) should
be avoided if possible.

Practically, if you find your large macro written like this, then make sure
you get expert help to review/rewrite it before you use it in production.

Cheers,
Chang

%macro dateRange(start, end);
%let start = %sysfunc(intnx(month, "01&start"d, 0), date9);
%let end = %sysfunc(intnx(month, "01&end"d, 1), date9);
"&start"d <= model < "&end"d
%mend dateRange;

%*-- check --*;
%put %dateRange(jan09, dec09);
%*-- on log
"01JAN2009"d <= model < "01JAN2010"d
--*;
From: Jack Hamilton on 13 Jan 2010 14:28
Remove the single quotes around 'month'.

The documentation for %SYSFUNC, at <http://support.sas.com/onlinedoc/913/getDoc/en/mcrolref.hlp/z3514sysfunc.htm>, is incorrect. It says that quotes around character arguments are not required (implying optional), when in fact they are not allowed.

Quotes can be used in the second argument because it's numeric, not character.

--
Jack Hamilton
jfh(a)alumni.stanford.org
Caelum non animum mutant qui trans mare currunt.

On Jan 13, 2010, at 10:53 am, Masoud Pajoh wrote:

> All:
> The following is a fragment of a much larger macro.
>
> %macro test( StartDate, EndDate);
> %let EndDate=%sysfunc(intnx('month',"&EndDate."d,1));
> %let StartDate=01&StartDate.;
> %let where="&StartDate."d<=model<="&EndDate."d;
> %mend(test);
>
> %test( jan09, dec09);
>
> WHERE should resolve to "01jan09"d<=model<="01jan10"d
> But, I get:
>
> WARNING: An argument to the function INTNX referenced by the %SYSFUNC or
> %QSYSFUNC macro function is out of range.
>
> What is wrong?
>
> Thanks,
>
> Masoud
From: Masoud Pajoh on 13 Jan 2010 14:47
Thank you all the help,

Masoud
 |  Next  |  Last
Pages: 1 2 3
Prev: how to pick the last 65000 rows
Next: Updated comparison table for SAS-SPSS Add-ons and R Functions