Date:         Thu, 18 Jun 2009 10:44:53 -0700
Reply-To:     "Nordlund, Dan (DSHS/RDA)" <NordlDJ@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Nordlund, Dan (DSHS/RDA)" <NordlDJ@DSHS.WA.GOV>
Subject:      Re: How to reference macro variable in PROC SQL
In-Reply-To:  <200906181723.n5IAkSEv018137@malibu.cc.uga.edu>
Content-Type: text/plain; charset=windows-1252

> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> Arthur Tabachneck
> Sent: Thursday, June 18, 2009 10:24 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: How to reference macro variable in PROC SQL
>
> Paul,
>
> Aside from the fact that the two are using different files (i.e.,
>
> >asdfasdf.dbo.BookingSummaryCurrent BookingSummaryCurrent
> vs.
> >F9Revenuemanagement_DEV.dbo.BookingSummaryCurrent
> BookingSummaryCurrent
>
> I'd think you'd have to enclose the dates in quotes (i.e.,
>
> >and DepartureDate between "&today" and "&end_date"
>
> HTH,
> Art
> ----------
> On Thu, 18 Jun 2009 10:04:10 -0700, Paul Lambson
> <paullambson@GMAIL.COM>
> wrote:
>
> >It's been a while since i've used the PROC SQL step with marco
> >variables. I'm not able to reference macro declared variables. Any
> >help will be appreciated.
> >
> >
> >THIS RETURNS RESULTS
> >
> >proc sql;
> >connect to odbc(dsn=gosqldw user=&user password=&password);
> >create table current as
> >select * from connection to odbc
> >(SELECT
> >ORG,
> >DEST,
> >DepartureDate,
> >sum(RPMS) as 'RPMS', sum(ASMS) as 'ASMS'
> >FROM
> >asdfasdf.dbo.BookingSummaryCurrent BookingSummaryCurrent
> >WHERE (BookingSummaryCurrent.DCP>0)
> >and DepartureDate between '06/20/2009' and '08/31/2009'
> >group by ORG, DEST, DepartureDate
> >order by ORG, DEST, DepartureDate
> >);
> >quit;
> >
> >
> >THIS RETURNS NO RESULTS
> >
> >%let end_date=%sysfunc(putn(%eval(%sysfunc(today())+90-%eval(%sysfunc
> >(day(%eval(%sysfunc(today()))+90)))),mmddyy10.));
> >%let today=%sysfunc(putn(%eval(%sysfunc(today())),mmddyy10.));
> >proc sql;
> >connect to odbc(dsn=gosqldw user=&user password=&password);
> >create table current as
> >select * from connection to odbc
> >(SELECT
> >ORG,
> >DEST,
> >DepartureDate,
> >sum(RPMS) as 'RPMS', sum(ASMS) as 'ASMS'
> >FROM
> >F9Revenuemanagement_DEV.dbo.BookingSummaryCurrent
> >BookingSummaryCurrent
> >WHERE (BookingSummaryCurrent.DCP>0)
> >and DepartureDate between &today and &end_date
> >group by ORG, DEST, DepartureDate
> >order by ORG, DEST, DepartureDate
> >);
> >quit;
> >
> >
> >Thanks,
> >
> >Paul

Quotes will be necessary, but the code is using pass-thru sql.  So, depending on the target database, single quotes may be necessary.  This creates problems for the resolution of the macro variable.  I am not sure I completely understand how end date is being calculated, but here are a couple of options.

Using the original code, wrap the original calculation with %str(%')

%let end_date=%str(%')%sysfunc(putn(%eval(%sysfunc(today())+90-%eval(%sysfunc(day(%eval(%sysfunc(today()))+90)))),mmddyy10.))%str(%');

Or, if I have guessed right on the calculation of end_date, a simplification might be

%let end_date=%str(%')%sysfunc(intnx(month,%sysfunc(today()),2,E),mmddyy10.)%str(%');

Hope this is helpful,

Dan

Daniel J. Nordlund
Washington State Department of Social and Health Services
Planning, Performance, and Accountability
Research and Data Analysis Division
Olympia, WA  98504-5204