Date:         Thu, 25 Mar 2010 22:44:39 -0400
Reply-To:     Jeff <zhujp98@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jeff <zhujp98@GMAIL.COM>
Subject:      Re: macro variable trouble
Comments: To: Jack Hamilton <jfh@stanfordalumni.org>
In-Reply-To:  <268AF13F-6545-4C91-A941-195AA98C5407@alumni.stanford.org>
Content-Type: text/plain; charset=ISO-8859-1

Thanks.
Jeff

On Thu, Mar 25, 2010 at 10:33 PM, Jack Hamilton <jfh@alumni.stanford.org>wrote:

> You can't use the INPUT and PUT functions inside %SYSFUNC.  Use INPUTN and
> PUTN instead.
>
> You don't need the PUT at all.  The %SYSFUNC function takes a format name
> as its second argument.  And "year" shouldn't be in quotes.  You might want
> to study the documentation.
>
>
>   %sysfunc(intnx(year, input(&dt, mmddyy10.), -1), mmddyy10.)
>
>
> --
> Jack Hamilton
> jfh@alumni.stanford.org
> Caelum non animum mutant qui trans mare currunt.
>
> On Mar 25, 2010, at 7:15 pm, Jeff wrote:
>
> > I want %sysfunc(put(intnx('year',input(&dt,mmddyy10.),-1),mmddyy10.))
> > resolve to '1/1/2008'
> > How can I do that?
> > Thanks,
> > Jeff
> >
> > 338    option mprint mlogic symbolgen;
> > 339    %let dt='1/1/2009';
> > 340   %macro main;
> > 341   proc sql noprint;
> > 342
> > 343     Connect to ODBC (DSN=MS uid="wmgt" pwd="ne20" bcp=yes);
> > 344       Execute
> > 345      (
> > 346
> > 347        select top 1000 * into #a
> > 348       from  dbo.Case
> > 349       where
> > openedate>%sysfunc(put(intnx('year',input(&dt,mmddyy10.),-1),mmddyy10.))
> > 350
> > 351      ) by odbc;
> > 352      create table t as select * from connection to odbc
> > 353      (
> > 354        select * from  #a
> > 355      );
> > 356       disconnect from odbc;
> > 357     quit;
> > 358  %mend;
> > 359  %main;
> > MLOGIC(MAIN):  Beginning execution.
> > MPRINT(MAIN):   proc sql noprint;
> > MPRINT(MAIN):   Connect to ODBC ();
> > SYMBOLGEN:  Macro variable DT resolves to '1/1/2009'
> > ERROR: The PUT function referenced in the %SYSFUNC or %QSYSFUNC macro
> > function is not found.
> > MPRINT(MAIN):   Execute ( select top 1000 * into #a from
> > dbo.CaseActivityByCase where openedate> ) by
> > odbc;
> > ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL
> > Server]Incorrect syntax near '>'.
> > MPRINT(MAIN):   create table t as select * from connection to odbc (
> select
> > * from #a );
> > ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL
> > Server]Invalid object name '#a'. :
> >       [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could
> not
> > be prepared.
> > MPRINT(MAIN):   disconnect from odbc;
> > MPRINT(MAIN):   quit;
> > NOTE: The SAS System stopped processing this step because of errors.
> > NOTE: PROCEDURE SQL used (Total process time):
> >      real time           0.28 seconds
> >      cpu time            0.00 seconds
> >
> > MLOGIC(MAIN):  Ending execution.
>
>