Date:         Sat, 2 Sep 2006 19:46:50 -0700
Reply-To:     toby989@HOTPOP.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby989@HOTPOP.COM
Subject:      macro variable created via a macro's proc sql select into does
              not work
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

I am getting strange results and en error message: Apparent symbolic reference
VARY not resolved.
Why do the things described in the sas help not work? My macro variable created
with the proc sql is supposed to be a local variable, as the help says, yet it
gets filled in the first iteration of the calling do loop with what I want to
have it filled, but in the x'th loop it has things repeated x times in it. If I
explicitly declare it %local, then nothing at all is put into this macro
variable, but at least the eroor message disappears.

All things work fine if I just issue the statements manually, i e not as part of
the macro calmac.

I have seen the last pages of:
http://www.cinsug.org/docs/frey/SELECTINTO.pdf#search=%22sas%20sql%20into%20macro%22
who has a similar example on his last pages, but I cant somehow get it to work.



NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds


15  +
proc corr data=psdata.ps2004ms(where=(id<200 and id not
16  + in (49, 109) and half=0)) alpha nocorr nosimple
outp=outcal(where=(_type_='STDALPHA')); var &vary &vary ; <-------WTF is that???
run;

WARNING: Variable q13 already exists on file WORK.OUTCAL.
WARNING: Variable q15 already exists on file WORK.OUTCAL.
WARNING: Variable q34 already exists on file WORK.OUTCAL.
WARNING: Variable q36 already exists on file WORK.OUTCAL.
WARNING: Cronbach's Alpha has been computed with missing values present. Using
the NOMISS option is
          recommended.
NOTE: The data set WORK.OUTCAL has 1 observations and 6 variables.
NOTE: PROCEDURE CORR used (Total process time):
       real time           0.14 seconds
       cpu time            0.14 seconds






%macro calmac(fct=);
proc sql;
select trim(left(_name_)) into :vary separated by ' ' from c where fac&fct=1;
quit;
%put _user_;
proc corr data=psdata.ps2004ms(where=(id<200 and id not in (49, 109) and
half=0)) alpha nocorr nosimple outp=outcal(where=(_type_='STDALPHA'));
var &vary ;
run;
proc transpose data=outcal(drop=_type_ _name_) out=outcal(drop=_name_); run;
proc sql;
insert into outc set _type_='STAT', _name_='STDALPHA', &m&n&priors&r=(select
distinct col1 from outcal);
quit;
%mend;

This macro i called form a doo loop that sits within another macro. This other
macro is called via call execute within iterative do's in a datastep;

%do ix=1 %to &n;
%calmac(fct=&ix);
%end;

84   run;
85   %mend;
86
87   data _null_;
88   i=1;
89   *do i='cov', 'corr';
90   do n=' 3';
91   do m='a';
92   do priors='a';
93   do r='n', 'q', 'v';
94   call
execute('%efamac(n='||n||',m='||m||',p='||p||',r='||r||',i='||left(i)||')');
95   i=i+1;
96   end;
97   end;
98   end;
99   end;
100  run;

NOTE: Numeric values have been converted to character values at the places given by:
       (Line):(Column).
       94:83
CALMAC FCT 1
EFAMAC IX 1
EFAMAC IX 1
EFAMAC M a
EFAMAC N 3
EFAMAC R n
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
CALMAC FCT 2
EFAMAC IX 1
EFAMAC IX 2
EFAMAC M a
EFAMAC N 3
EFAMAC R n
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
CALMAC FCT 3
EFAMAC IX 1
EFAMAC IX 3
EFAMAC M a
EFAMAC N 3
EFAMAC R n
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
CALMAC FCT 1
EFAMAC IX 2
EFAMAC IX 1
EFAMAC M a
EFAMAC N 3
EFAMAC R q
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
CALMAC FCT 2
EFAMAC IX 2
EFAMAC IX 2
EFAMAC M a
EFAMAC N 3
EFAMAC R q
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
CALMAC FCT 3
EFAMAC IX 2
EFAMAC IX 3
EFAMAC M a
EFAMAC N 3
EFAMAC R q
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
CALMAC FCT 1
EFAMAC IX 3
EFAMAC IX 1
EFAMAC M a
EFAMAC N 3
EFAMAC R v
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
CALMAC FCT 2
EFAMAC IX 3
EFAMAC IX 2
EFAMAC M a
EFAMAC N 3
EFAMAC R v
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
CALMAC FCT 3
EFAMAC IX 3
EFAMAC IX 3
EFAMAC M a
EFAMAC N 3
EFAMAC R v
EFAMAC P a
GLOBAL VARIABLE 1
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
WARNING: Apparent symbolic reference VARY not resolved.
NOTE: DATA statement used (Total process time):
       real time           0.03 seconds
       cpu time            0.03 seconds