Date: Fri, 10 Mar 2000 10:32:43 -0000
Reply-To: "Vyverman, Koen" <koen.vyverman@FID-INTL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Vyverman, Koen" <koen.vyverman@FID-INTL.COM>
Subject: SAS/CONNECT bug? (was Re: PROC SQL remote submit error)
Content-Type: text/plain; charset="iso-8859-1"
LS,
A week ago, I reported some strange SAS behaviour related
to SQL statements containing macro variable references being
remotely submitted. In the meantime I discussed the topic
further with Ian Whitlock, who suggested creating a code
sample package demonstrating the problem.
I include the code below the .sig, as well as the SAS log.
I'd be interested to learn if any users of other versions
and/or platforms experience the same behaviour when running
the code. I use 6.12 for Win95 here, TS060. The remote host
is a UNIX box, running 6.12 TS020.
Description of the problem: Suppose a macro generates re-
motely submitted SQL statements that reference one or more
macro variables. Suppose the LINESIZE option is set to N.
If the Nth character of the SQL statement happens to be with-
in one of the referenced macro variables, this macro token
will fail to be resolved, and the code bombs. As long as the
Nth character of the SQL statement is not part of any macro
variable reference, all works fine.
I could not find this odd behaviour documented anywhere.
Therefore I consider it a bug. Any other experiences or
comments?
Best Regards,
Koen.
---------------------------------
Koen Vyverman
Marketing Database Manager
Fidelity Investments - Luxembourg
Phone : (++352) 250 404 22 63
Fax : (++352) 250 404 22 08
---------------------------------
============================================================
SAS Code:
============================================================
/*--------------------------------------------------------
Some code to demonstrate the fact that LINESIZE is
important when running remote SQL queries containing
macro variables... At least if a macro is used to
generate the code!
--------------------------------------------------------*/
%* Some dummy data: *;
data _dumb;
input
varble01 varble02 varble03 :8.
;
cards;
412708 486543 228765
317179 227654 983453
238880 365436 334544
415871 154349 432878
313246 223876 661232
274836 767877 376588
364223 343321 223124
273732 115466 736549
230294 234355 576651
;
run;
%* Upload it... *;
rsubmit;
proc upload data=_dumb
out=_dumb;
run;
endrsubmit;
%* Macro variables, local and remote... *;
%let mvar0001=varble01;
%let mvar0002=varble02;
%let mvar0003=varble03;
%syslput(variable=mvar0001,value=&mvar0001);
%syslput(variable=mvar0002,value=&mvar0002);
%syslput(variable=mvar0003,value=&mvar0003);
%* Specify an initial LINESIZE. *;
options
linesize=75
source
source2
nomprint
nomlogic
symbolgen;
rsubmit;
options
linesize=75
source
source2
nomprint
nomlogic
symbolgen;
endrsubmit;
%* A local SQL query using the macro variables. It works *;
%* fine. *;
proc sql;
create table _dumber as
select
&mvar0001 as var1,
&mvar0002 as var2,
&mvar0003 as var3,
&mvar0001 as var1too,
&mvar0002 as var2too,
&mvar0003 as var3too
from
_dumb
;
quit;
%* The same query, executed remotely. This also works *;
%* fine. *;
rsubmit;
proc sql;
create table _dumber as
select
&mvar0001 as var1,
&mvar0002 as var2,
&mvar0003 as var3,
&mvar0001 as var1too,
&mvar0002 as var2too,
&mvar0003 as var3too
from
_dumb
;
quit;
endrsubmit;
%* The same remote query, but now in a simple macro *;
%* wrapper. No reason why this should not work, right? *;
%* But it does not... Now, note that the LINESIZE setting *;
%* was 75, and the 75th character in the SQL create sta- *;
%* tement is the R in the first reference to the macro *;
%* variable MVAR0003. From the log we see that the first *;
%* occurrence of &MVAR0003 is not resolved. *;
%macro testit;
rsubmit;
proc sql;
create table _dumber as
select
&mvar0001 as var1,
&mvar0002 as var2,
&mvar0003 as var3,
&mvar0001 as var1too,
&mvar0002 as var2too,
&mvar0003 as var3too
from
_dumb
;
quit;
endrsubmit;
%mend testit;
%testit;
%* Increment LINESIZE and try again. Failure, once more. *;
%* The 76th character of the SQL statement is now the *;
%* first zero in the token &MVAR0003. Again, &MVAR0003 is *;
%* not being resolved. *;
options linesize=76;
rsubmit;
options linesize=76;
endrsubmit;
%testit;
%* Increment LINESIZE and try again. Failure... *;
%* The 77th character of the SQL statement corresponds to *;
%* the second zero in the token &MVAR0003. Here too, *;
%* &MVAR0003 remains unresolved. *;
options linesize=77;
rsubmit;
options linesize=77;
endrsubmit;
%testit;
%* Increment LINESIZE and try again. Failure... *;
%* The 78th character of the SQL statement corresponds to *;
%* the third zero in the token &MVAR0003, which remains *;
%* unresolved... *;
options linesize=78;
rsubmit;
options linesize=78;
endrsubmit;
%testit;
%* Increment LINESIZE and try again. Miracle! It works! *;
%* Note that the 79th character of the SQL statement is *;
%* now the final 3 in the token &MVAR0003, which finally *;
%* resolves properly. *;
options linesize=79;
rsubmit;
options linesize=79;
endrsubmit;
%testit;
============================================================
SAS LOG generated by the above:
============================================================
445 /*--------------------------------------------------------
446
447 Some code to demonstrate the fact that LINESIZE is
448 important when running remote SQL queries containing
449 macro variables... At least if a macro is used to
450 generate the code!
451
452 --------------------------------------------------------*/
453
454
455 %* Some dummy data: *;
456 data _dumb;
457 input
458 varble01 varble02 varble03 :8.
459 ;
460 cards;
NOTE: The data set WORK._DUMB has 9 observations and 3 variables.
NOTE: The DATA statement used 0.05 seconds.
470 ;
471 run;
472
473
474 %* Upload it... *;
475 rsubmit;
NOTE: Remote submit to MYNODE commencing.
5860 proc upload data=_dumb
5861 out=_dumb;
5862 run;
NOTE: Upload in progress from data=WORK._DUMB to out=WORK._DUMB
NOTE: 216 bytes were transferred at 982 bytes/second.
NOTE: The data set WORK._DUMB has 9 observations and 3 variables.
NOTE: Uploaded 9 observations of 3 variables.
NOTE: The data set WORK._DUMB has 9 observations and 3 variables.
NOTE: PROCEDURE UPLOAD used:
real time 0.390 seconds
cpu time 0.006 seconds
NOTE: Remote submit to MYNODE complete.
476
477
478 %* Macro variables, local and remote... *;
479 %let mvar0001=varble01;
480 %let mvar0002=varble02;
481 %let mvar0003=varble03;
482 %syslput(variable=mvar0001,value=&mvar0001);
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable VARIABLE resolves to mvar0001
SYMBOLGEN: Macro variable VALUE resolves to varble01
SYMBOLGEN: Macro variable __RSUB resolves to rsubmit;
%let mvar0001=varble01; endrsubmit;
SYMBOLGEN: Some characters in the above value which were subject to macro
quoting have been unquoted for printing.
NOTE: Remote submit to MYNODE commencing.
5863 %let mvar0001=varble01;
NOTE: Remote submit to MYNODE complete.
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
SYMBOLGEN: Macro variable VARIABLE resolves to mvar0002
SYMBOLGEN: Macro variable VALUE resolves to varble02
483 %syslput(variable=mvar0002,value=&mvar0002);
SYMBOLGEN: Macro variable __RSUB resolves to rsubmit;
%let mvar0002=varble02; endrsubmit;
SYMBOLGEN: Some characters in the above value which were subject to macro
quoting have been unquoted for printing.
NOTE: Remote submit to MYNODE commencing.
5864 %let mvar0002=varble02;
NOTE: Remote submit to MYNODE complete.
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
SYMBOLGEN: Macro variable VARIABLE resolves to mvar0003
SYMBOLGEN: Macro variable VALUE resolves to varble03
484 %syslput(variable=mvar0003,value=&mvar0003);
SYMBOLGEN: Macro variable __RSUB resolves to rsubmit;
%let mvar0003=varble03; endrsubmit;
SYMBOLGEN: Some characters in the above value which were subject to macro
quoting have been unquoted for printing.
NOTE: Remote submit to MYNODE commencing.
5865 %let mvar0003=varble03;
NOTE: Remote submit to MYNODE complete.
485
486
487 %* Specify an initial LINESIZE. *;
488 options
489 linesize=75
490 source
491 source2
492 nomprint
493 nomlogic
494 symbolgen;
495 rsubmit;
NOTE: Remote submit to MYNODE commencing.
5866 options
5867 linesize=75
5868 source
5869 source2
5870 nomprint
5871 nomlogic
5872 symbolgen;
NOTE: Remote submit to MYNODE complete.
496
497
498 %* A local SQL query using the macro variables. It works *;
499 %* fine. *;
500 proc sql;
501 create table _dumber as
502 select
503 &mvar0001 as var1,
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
504 &mvar0002 as var2,
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
505 &mvar0003 as var3,
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
506 &mvar0001 as var1too,
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
507 &mvar0002 as var2too,
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
508 &mvar0003 as var3too
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
509 from
510 _dumb
511 ;
NOTE: Table WORK._DUMBER created, with 9 rows and 6 columns.
512 quit;
NOTE: The PROCEDURE SQL used 0.88 seconds.
513
514
515 %* The same query, executed remotely. This also works *;
516 %* fine. *;
517 rsubmit;
NOTE: Remote submit to MYNODE commencing.
5873 proc sql;
5874 create table _dumber as
5875 select
5876 &mvar0001 as var1,
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
5877 &mvar0002 as var2,
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
5878 &mvar0003 as var3,
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
5879 &mvar0001 as var1too,
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
5880 &mvar0002 as var2too,
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
5881 &mvar0003 as var3too
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
5882 from
5883 _dumb
5884 ;
NOTE: Table WORK._DUMBER created, with 9 rows and 6 columns.
5885 quit;
NOTE: PROCEDURE SQL used:
real time 0.100 seconds
cpu time 0.009 seconds
NOTE: Remote submit to MYNODE complete.
518
519
520 %* The same remote query, but now in a simple macro *;
521 %* wrapper. No reason why this should not work, right? *;
522 %* But it does not... Now, note that the LINESIZE setting *;
523 %* was 75, and the 75th character in the SQL create sta- *;
524 %* tement is the R in the first reference to the macro *;
525 %* variable MVAR0003. From the log we see that the first *;
526 %* occurrence of &MVAR0003 is not resolved. *;
527 %macro testit;
528 rsubmit;
529 proc sql;
530 create table _dumber as
531 select
532 &mvar0001 as var1,
533 &mvar0002 as var2,
534 &mvar0003 as var3,
535 &mvar0001 as var1too,
536 &mvar0002 as var2too,
537 &mvar0003 as var3too
538 from
539 _dumb
540 ;
541 quit;
542 endrsubmit;
543 %mend testit;
544
545
546 %testit;
NOTE: Remote submit to MYNODE commencing.
5886 proc sql;
5887 create table _dumber as select &mvar0001 as var1, &mvar0002 as
var2, &
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
5887 create table _dumber as select &mvar0001 as var1, &mvar0002 as
var2, &
-
80
ERROR 80-322: Expecting a variable name.
5888 mvar0003 as var3, &mvar0001 as var1too, &mvar0002 as var2too,
&mvar0003 as
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
5889 var3too from _dumb ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.
5890 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
real time 0.010 seconds
cpu time 0.004 seconds
NOTE: Remote submit to MYNODE complete.
547
548
549 %* Increment LINESIZE and try again. Failure, once more. *;
550 %* The 76th character of the SQL statement is now the *;
551 %* first zero in the token &MVAR0003. Again, &MVAR0003 is *;
552 %* not being resolved. *;
553 options linesize=76;
554 rsubmit;
NOTE: Remote submit to MYNODE commencing.
5891 options linesize=76;
NOTE: Remote submit to MYNODE complete.
555
556
557 %testit;
NOTE: Remote submit to MYNODE commencing.
5892 proc sql;
5893 create table _dumber as select &mvar0001 as var1, &mvar0002 as
var2, &
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
5893 create table _dumber as select &mvar0001 as var1, &mvar0002 as
var2, &
-
80
ERROR 80-322: Expecting a variable name.
5894 mvar0003 as var3, &mvar0001 as var1too, &mvar0002 as var2too,
&mvar0003 as
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
5895 var3too from _dumb ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.
5896 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
real time 0.000 seconds
cpu time 0.003 seconds
NOTE: Remote submit to MYNODE complete.
558
559
560 %* Increment LINESIZE and try again. Failure... *;
561 %* The 77th character of the SQL statement corresponds to *;
562 %* the second zero in the token &MVAR0003. Here too, *;
563 %* &MVAR0003 remains unresolved. *;
564 options linesize=77;
565 rsubmit;
NOTE: Remote submit to MYNODE commencing.
5897 options linesize=77;
NOTE: Remote submit to MYNODE complete.
566
567
568 %testit;
NOTE: Remote submit to MYNODE commencing.
5898 proc sql;
5899 create table _dumber as select &mvar0001 as var1, &mvar0002 as var2,
&
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
5899 create table _dumber as select &mvar0001 as var1, &mvar0002 as var2,
&
-
80
ERROR 80-322: Expecting a variable name.
5900 mvar0003 as var3, &mvar0001 as var1too, &mvar0002 as var2too,
&mvar0003 as
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
5901 var3too from _dumb ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.
5902 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
real time 0.010 seconds
cpu time 0.005 seconds
NOTE: Remote submit to MYNODE complete.
569
570
571 %* Increment LINESIZE and try again. Failure... *;
572 %* The 78th character of the SQL statement corresponds to *;
573 %* the third zero in the token &MVAR0003, which remains *;
574 %* unresolved... *;
575 options linesize=78;
576 rsubmit;
NOTE: Remote submit to MYNODE commencing.
5903 options linesize=78;
NOTE: Remote submit to MYNODE complete.
577
578
579 %testit;
NOTE: Remote submit to MYNODE commencing.
5904 proc sql;
5905 create table _dumber as select &mvar0001 as var1, &mvar0002 as var2,
&
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
5905 create table _dumber as select &mvar0001 as var1, &mvar0002 as var2,
&
-
80
ERROR 80-322: Expecting a variable name.
5906 mvar0003 as var3, &mvar0001 as var1too, &mvar0002 as var2too,
&mvar0003 as
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
5907 var3too from _dumb ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.
5908 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
real time 0.010 seconds
cpu time 0.003 seconds
NOTE: Remote submit to MYNODE complete.
580
581
582 %* Increment LINESIZE and try again. Miracle! It works! *;
583 %* Note that the 79th character of the SQL statement is *;
584 %* now the final 3 in the token &MVAR0003, which finally *;
585 %* resolves properly. *;
586 options linesize=79;
587 rsubmit;
NOTE: Remote submit to MYNODE commencing.
5909 options linesize=79;
NOTE: Remote submit to MYNODE complete.
588
589
590 %testit;
NOTE: Remote submit to MYNODE commencing.
5910 proc sql;
5911 create table _dumber as select &mvar0001 as var1, &mvar0002 as var2,
&mvar0003
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
5912 as var3, &mvar0001 as var1too, &mvar0002 as var2too, &mvar0003 as
var3too from
SYMBOLGEN: Macro variable MVAR0001 resolves to varble01
SYMBOLGEN: Macro variable MVAR0002 resolves to varble02
SYMBOLGEN: Macro variable MVAR0003 resolves to varble03
5913 _dumb ;
NOTE: Table WORK._DUMBER created, with 9 rows and 6 columns.
5914 quit;
NOTE: PROCEDURE SQL used:
real time 0.090 seconds
cpu time 0.008 seconds
NOTE: Remote submit to MYNODE complete.
