How to reference macro variable in PROC SQL

Discussion:

(too old to reply)

Paul Lambson

2009-06-18 17:04:10 UTC

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

"Richard A. DeVenezia"

2009-06-18 17:21:11 UTC

Permalink

Post by Paul Lambson
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;

You might consider this:
%let today = %sysfunc(today());
%let end_dateQ = %str(%')%sysfunc(intnx(month,&today,3),mmddyy10)%str
(%');
%let todayQ = %str(%')%sysfunc(today(),mmddyy10)%str(%');

... between &todayQ and &end_dateQ ...

--
Richard A. DeVenezia
http://www.devenezia.com

Arthur Tabachneck

2009-06-18 17:23:44 UTC

Permalink

Paul,

Aside from the fact that the two are using different files (i.e.,

Post by Paul Lambson
asdfasdf.dbo.BookingSummaryCurrent BookingSummaryCurrent

vs.

Post by Paul Lambson
F9Revenuemanagement_DEV.dbo.BookingSummaryCurrent BookingSummaryCurrent

I'd think you'd have to enclose the dates in quotes (i.e.,

Post by Paul Lambson
and DepartureDate between "&today" and "&end_date"

HTH,
Art
----------

Ya Huang

2009-06-18 17:19:52 UTC

Permalink

It seems that you missed the quotation mark:

and DepartureDate between "&today" and "&end_date"

Nordlund, Dan (DSHS/RDA)

2009-06-18 17:44:53 UTC

Permalink

-----Original Message-----
Arthur Tabachneck
Sent: Thursday, June 18, 2009 10:24 AM
Subject: Re: How to reference macro variable in PROC SQL
Paul,
Aside from the fact that the two are using different files (i.e.,

Post by Paul Lambson
asdfasdf.dbo.BookingSummaryCurrent BookingSummaryCurrent

vs.

Post by Paul Lambson
F9Revenuemanagement_DEV.dbo.BookingSummaryCurrent

BookingSummaryCurrent
I'd think you'd have to enclose the dates in quotes (i.e.,

Post by Paul Lambson
and DepartureDate between "&today" and "&end_date"

HTH,
Art
----------
On Thu, 18 Jun 2009 10:04:10 -0700, Paul Lambson

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

Paul Lambson

2009-06-18 21:22:02 UTC

Permalink

Post by Paul Lambson
asdfasdf.dbo.BookingSummaryCurrent BookingSummaryCurrent

vs.

Post by Paul Lambson
F9Revenuemanagement_DEV.dbo.BookingSummaryCurrent

BookingSummaryCurrent
I'd think you'd have to enclose the dates in quotes (i.e.,

Post by Paul Lambson
and DepartureDate between "&today" and "&end_date"

HTH,
Art
----------
On Thu, 18 Jun 2009 10:04:10 -0700, Paul Lambson

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

Dan,

The %str(%') worked perfectly.

Thanks,

Paul