Date:         Sat, 6 Jun 2009 12:58:34 -0400
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: how to pass this string string to macro call
Comments: To: JP Zhu <zhujp98@GMAIL.COM>

Jeff,

I probably don't correctly understand what you are looking for.  The code,
sample data, and results I got were:

libname cmsdmart "c:\art";

%macro CalculateMetrics012(x,y,themlist,theylist);
  proc sql;
    create table test as &themlist.
;
quit;
%mend;

%let end=6jun2009;
%let mlist=(select timeid from cmsdmart.TimeByDay where thedate="&end"d-1);
%let ylist=(select timeid from cmsdmart.TimeByDay where thedate="&end"d-1);

data cmsdmart.Timebyday;
  input timeid thedate date9.;
  cards;
122 5jun2009
123 6jun2009
124 7jun2009
;

%CalculateMetrics012(1,2,%str(&mlist), %str(&ylist))
proc print data=test;
run;

         Obs    timeid

          1       122

Since you only want a day before an indicated date, you only have to
subtract 1 from that date.  Intnx is returning boundaries crossed, not
dates.

Art
--------
On Sat, 6 Jun 2009 12:10:39 -0400, Jeff <zhujp98@GMAIL.COM> wrote:

>Thank you for your email. Arthur.
>%let mlist=(select timeid from cmsdmart.TimeByDay where thedate="&end"d-1);
>is not what I wanted.
>here (select timeid from cmsdmart.TimeByDay where thedate="&end"d) will
>return a single number, for examples 123 which is the time id of then day
>&end. I want timeid 122 which is from the day before &end.
>I tried
>mlist=(select timeid from cmsdmart.TimeByDay where thedate=intnx('day',
>"&end"d,-1)
>
>I change your code to:
>
>%macro CalculateMetrics012(x,y,themlist,theylist);
> proc sql;
>   create table test as &themlist.
>;
>quit;
>%mend;
>%let end=6jun2009;
>%let mlist=(select timeid from cmsdmart.TimeByDay where
>thedate=intnx('day',"&end"d,-1);
>%let ylist=(select timeid from cmsdmart.TimeByDay where
>thedate=intnx('day',"&end"d,-1);
>%CalculateMetrics012(1,2,%str(&mlist), %str(&ylist))
>
>
>When I run this progam I get log like
>
>
>
>1    %macro CalculateMetrics012(x,y,themlist,theylist);
>2     proc sql;
>3       create table test as &themlist.
>4    ;
>5    quit;
>6    %mend;
>7    %let end=6jun2009;
>8    %let mlist=(select timeid from cmsdmart.TimeByDay where
>8  ! thedate=intnx('day',"&end"d,-1);
>9    %let ylist=(select timeid from cmsdmart.TimeByDay where
>9  ! thedate=intnx('day',"&end"d,-1);
>10   %CalculateMetrics012(1,2,%str(&mlist), %str(&ylist))
>
>
>any the progam stalled here.
>why the program stalled in this way? and Is there a solution to this
>problem?
>
>Thanks.
>Jeff
>
>
>On Sat, Jun 6, 2009 at 10:23 AM, Arthur Tabachneck
<art297@netscape.net>wrote:
>
>> Jeff,
>>
>> Is there a chance that it is your ending paraenthesis rather than the
minus
>> sign that is causing the error?
>>
>> I don't know if the following approximates what you are trying to do, but
>> it
>> works:
>>
>> %macro CalculateMetrics012(x,y,themlist,theylist);
>>  proc sql;
>>    create table test as &themlist.
>> ;
>> quit;
>> %mend;
>> %let end=6jun2009;
>> %let mlist=(select timeid from cmsdmart.TimeByDay where thedate="&end"d-
1);
>> %let ylist=(select timeid from cmsdmart.TimeByDay where thedate="&end"d-
1);
>> %CalculateMetrics012(1,2,%str(&mlist), %str(&ylist))
>>
>> Art
>> ----------
>> On Fri, 5 Jun 2009 23:47:19 -0400, Jeff <zhujp98@GMAIL.COM> wrote:
>>
>> >I want to pass mlist which contain "-" as entire string to macro call.
How
>> >can I do that?
>> >THX
>> >Jeff
>> >
>> >
>> >552
>> >553
>> >554  %let mlist=(select timeid from cmsdmart.TimeByDay where
>> >thedate="&end"d)-1;
>> >555  %let ylist=(select timeid from cmsdmart.TimeByDay where
>> >thedate="&end"d)-1;
>> >556  %CalculateMetrics012(1,2,%str(&mlist), %str(&ylist));
>> >NOTE: Line generated by the macro variable "MTLIST".
>> >1     (select timeid from cmsdmart.TimeByDay where
thedate="30apr2009"d)-1
>> >
-
>> >
22
>> >
-
>> >
>> 76
>> >ERROR 22-322: Syntax error, expecting one of the following: !, &, AND,
>> >GROUP, HAVING, OR, ORDER,
>> >              |.
>> >
>> >ERROR 76-322: Syntax error, statement will be ignored.
>> >
>> >NOTE: The SAS System stopped processing this step because of errors.
>> >NOTE: PROCEDURE SQL used (Total process time):
>> >      real time           0.00 seconds
>> >      cpu time            0.00 seconds
>> >
>> >
>> >
>> >NOTE: Line generated by the macro variable "YRLIST".
>> >1     (select timeid from cmsdmart.TimeByDay where
thedate="30apr2009"d)-1
>> >
-
>> >
22
>> >
-
>> >
>> 76
>> >ERROR 22-322: Syntax error, expecting one of the following: !, &, AND,
>> >GROUP, HAVING, OR, ORDER,
>> >              |.
>> >
>> >ERROR 76-322: Syntax error, statement will be ignored.
>>