Date:         Tue, 17 Nov 2009 16:39:27 -0600
Reply-To:     Yu Zhang <zhangyu05@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Yu Zhang <zhangyu05@GMAIL.COM>
Subject:      Re: string convestion
Comments: To: Jeff <zhujp98@gmail.com>
In-Reply-To:  <6716d5d0911171424s526d70a2s282f603c1d462970@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

Jeff,

try

%bquote('&BeginQTR' );

or %unquote(%str(%')&BeginQTR%str(%'))


Yu

On Tue, Nov 17, 2009 at 4:24 PM, Jeff <zhujp98@gmail.com> wrote:

> code
>
>
> *
>
> data* _null_;
>
> format x y mmddyy10.;
>
> x=date(); *** This is the current date. It can be any data you choose.
>
> *** Remember to hard-code a date you enter '10OCT2009'd;
>
> format y MMDDYY10.;
>
> y=INTNX('QTR',x,*0*, 'Beg');
>
> put 'Original date: ' x ' Beginning QTR date: ' y;
>
> call symput ('beginQTR',put( y,MMDDYY10.));
>
> y=INTNX('QTR',x,*0*, 'End');
>
> put 'Original date: ' x ' Ending QTR date: ' y;
>
> call symput ('endQTR',put( y,MMDDYY10.));
>
> put;
>
> *run*;
>
> %put &BeginQTR;
>
> %PUT &ENDQTR;
>
> options mprint symbolgen;
> *
>
> %macro* *test*;
>
> proc sql;
>
> Connect to ODBC (DSN=xxx uid="xxxx" pwd="xxxx" bcp=yes);
>
>  create table tt1 as select * from connection to odbc
>
> (select * from CaseActivityByCase_CancerCoaching where
> OpenedDate>'&BeginQTR' );
>
> disconnect from odbc;
>
> quit;
> *
>
> %mend*;
>
> %*test*;
>
>
>
>
>
> log:
>
>  * now grab the resultset from a permanent table;
> MPRINT(TEST):   create table tt1 as select * from connection to odbc
> (select
> * from
> CaseActivityByCase_CancerCoaching where OpenedDate>'&BeginQTR' );
> ERROR: CLI open cursor error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Conversion failed when
>       converting datetime from character string.
>
> I want select * from CaseActivityByCase_CancerCoaching where
> OpenedDate>'&BeginQTR'
>
> to be solved as select * from CaseActivityByCase_CancerCoaching where
> OpenedDate>'10/1/2009'
>
> How can I do that?
>
> Thanks.
>