by Listers:
Things are not always as they appear to be. The QUIT; wasn't being ignored,
rather the cartesian product created by the join was large enough that it
wasn't finishing before the web server request timed out. I had conditioned
the where statement that would have limited the number of obs created by the
join. To be correct, I should have also conditioned the ", basedata.rptlvl"
in the FROM clause as well.
Rather obvious once I dumped the statement into PC SAS.
Thanks for the suggestions I received.
From: Droogendyk, Harry
Sent: April 11, 2001 11:39 AM
To: SA...@LISTSERV.UGA.EDU <mailto:SA...@LISTSERV.UGA.EDU>
Subject: %if in the middle of PROC SQL ain't workin'
Macro mavens:
SAS 6.12, SAS/Intrnet environment, Win 98
I'm attempting to make part of my where clause conditional on the value of a
macro variable. However, SAS doesn't appear to handle this too well. The
actual SQL statement is built as I'd expect, but the QUIT; is never
executed. Consequently, my SAS/Intrnet session just sits there, waiting for
the QUIT;
In an attempt to debug this thing I've added the %do; / %end; stuff even
when it's not strictly necessary.
Can anyone set me straight?
Here's the program chunk and log.
%macro sql;
%if &typ = P %then %do;
/*******************************************************
Totally different SQL for profile and "regular" users.
The profile userids are made up of segment and
titleid. Segment will be the surname, title
description the given name.
********************************************************/
proc sql;
create table buscard as
select distinct
case "&seg"
when "P" then "Priv. Bank."
when "W" then "Wealth"
when "R" then "Retail"
when "S" then "Small Bus."
when "W" then "Wealth"
else "Unknown"
end as surname,
titledsc as givename, userid, a.titleid
from basedata.buscard as a,
basedata.title as b
where "&seg" eq substr(userid,1,1)
and verify(substr(userid,2,3),'0123456789') = 0
and substr(userid,2,3) = b.titleid
order by surname, givename, userid ;
quit;
%end; %else %do;
/***********************************************************
If we have type of F, we're looking at portfolio users,
their startlvl ends in a .1, otherwise we're looking
ordinary segment guys, they end in zero ( or more
correctly, don't have a decimal ). Grab everybody
that pertains to the segment and type.
************************************************************/
%if &typ = F %then %do;
%let decimal = .1;
%end; %else %do;
%let decimal = 0;
%end;
proc sql;
create table buscard as
select distinct surname, givename, userid, titleid
from basedata.buscard, basedata.rptlvl
where upcase(userid) ne upcase("&userid")
and round(buscard.rptlvl - int(buscard.rptlvl),0.1) =
input("&decimal",2.1)
%if &seg ne D %then %do;
and buscard.rptlvl = rptlvl.startlvl and "&seg" eq
rptlvl.segment
%end;
order by userid, surname, givename ;
quit;
%end;
%mend sql;
%sql;
Log:
933
934 %macro sql;
935
936 %if &typ = P %then %do;
937
938 /*******************************************************
939 Totally different SQL for profile and "regular" users.
940 The profile userids are made up of segment and
941 titleid. Segment will be the surname, title
942 description the given name.
943 ********************************************************/
944
945 proc sql;
946 create table buscard as
947 select distinct
948 case "&seg"
949 when "P" then "Priv. Bank."
950 when "W" then "Wealth"
951 when "R" then "Retail"
952 when "S" then "Small Bus."
953 when "W" then "Wealth"
954 else "Unknown"
955 end as surname,
956 titledsc as givename, userid, a.titleid
957 from basedata.buscard as a,
958 basedata.title as b
959 where "&seg" eq substr(userid,1,1)
960 and verify(substr(userid,2,3),'0123456789') = 0
961 and substr(userid,2,3) = b.titleid
962 order by surname, givename, userid ;
963 quit;
964 %end; %else %do;
965
966 /***********************************************************
967 If we have type of F, we're looking at portfolio users,
968 their startlvl ends in a .1, otherwise we're looking
969 ordinary segment guys, they end in zero ( or more
970 correctly, don't have a decimal ). Grab everybody
971 that pertains to the segment and type.
972 ************************************************************/
973
974 %if &typ = F %then %do;
975 %let decimal = .1;
976 %end; %else %do;
977 %let decimal = 0;
978 %end;
979
980 proc sql;
981 create table buscard as
982 select distinct surname, givename, userid, titleid
983 from basedata.buscard, basedata.rptlvl
984 where upcase(userid) ne upcase("&userid")
985 and round(buscard.rptlvl - int(buscard.rptlvl),0.1) =
input("&decimal",2.1)
986 %if &seg ne D %then %do;
987 and buscard.rptlvl = rptlvl.startlvl and "&seg" eq
rptlvl.segment
988 %end;
989 order by userid, surname, givename ;
990 quit;
991
992 %end;
993
994 %mend sql;
995
996 %sql;
MLOGIC(SQL): Beginning execution.
SYMBOLGEN: Macro variable TYP resolves to
MLOGIC(SQL): %IF condition &typ = P is FALSE
SYMBOLGEN: Macro variable TYP resolves to
MLOGIC(SQL): %IF condition &typ = F is FALSE
MLOGIC(SQL): %LET (variable name is DECIMAL)
MPRINT(SQL): PROC SQL;
SYMBOLGEN: Macro variable USERID resolves to
SYMBOLGEN: Macro variable DECIMAL resolves to 0
SYMBOLGEN: Macro variable SEG resolves to D
MLOGIC(SQL): %IF condition &seg ne D is FALSE
MPRINT(SQL): CREATE TABLE BUSCARD AS SELECT DISTINCT SURNAME, GIVENAME,
USERID, TITLEID FROM BASEDATA.BUSCARD, BASEDATA.RPTLVL WHERE UPCASE(USERID)
NE UPCASE(" ") AND ROUND(BUSCARD.RPTLVL -
INT(BUSCARD.RPTLVL),0.1) = INPUT("0",2.1) ORDER BY USERID, SURNAME, GIVENAME
;
NOTE: The data set WORK.BUSCARD has 0 observations and 4 variables.
WARNING: Closing file !0000000 left open by program