Date:         Wed, 20 Aug 2008 12:21:55 -0700
Reply-To:     "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:      Re: macro variable generated by Proc SQL
Comments: To: adjmost@COMCAST.NET
In-Reply-To:  A<16FD64291482A34F995D2AF14A5C932C044F52A5@MAIL002.prod.ds.russell.com>
Content-Type: text/plain; charset="us-ascii"

Hi G,

Misread your question.  You also have
a scope issue.  Any macro variables
created inside a %Macro/%mend are
LOCAL to the inside of the macro and
not available outside the macro. If
you are trying to find them after the
macro is over you will need to add a
%global mymacro1; for each macro variable
and since you are creating them in a loop
and the name is affected by the loop index
then you will need to put the %GLOBAL
inside the %DO loop so the name will be
dynamically changed.  You can prove this
to be the issue if you place the %PUT
outside(after) and you don't get any
results and if you place the %PUT inside
the macro you will probably have results.
So use %GLOBAL so that they will be
available after the macro.

Mark


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Terjeson, Mark
Sent: Wednesday, August 20, 2008 12:05 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: macro variable generated by Proc SQL

Hi G,

To create variables in SQL is the "AS",
in SAS to create macro variables the
syntax is "into :"

e.g.

proc sql noprint;
    select min(age) into :MyMacroVar
    from sashelp.class;
quit;
%put MyMacroVar is >&MyMacroVar<;




Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst
Investment Management & Research
Russell Investments


Russell Investments
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
adjmost@COMCAST.NET
Sent: Wednesday, August 20, 2008 11:27 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: macro variable generated by Proc SQL

Hi,

I'm having some trouble with generating macro variables from a SQL
procedure.
I've done that in the past and never had problems, however here I'm
adding a couple of layers of complexity and there must be something
I'm doing wrong.
Here's how the code looks like, the macro variables &id, &i and &&id&i
have been defined in a previous step and a %PUT _USER_ statement shows
they exists and are correctly defined. the macro variables that are
giving me problems are those in the "into" part of the code.

%macro chartdata;
 %do i=1 %to &tot;
proc sql;
select min(date) as mindate, max(date)as maxdate, (select dos from
chartdata_&&id&i where l=1) as lastdos
into :first_&&id&i, :last_&&id&i, :lastdos_&&id&i
from chartdata_&&id&i
;
quit;
run;

%end;
%mend chartdata;

%chartdata;

When I run
%PUT _USER_;
I don't see any of the variables that were supposed to be created in
the Proc SQL step.
Any ideas? Is there a limit on how many macro variables SAS can
create?

Thanks,

G