Date:         Mon, 24 Jul 2006 15:08:29 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Proc SQL INTO Separated to create multiple macro variables
Comments: To: widad2020@GMAIL.COM
In-Reply-To:  <1153750761.948157.312970@p79g2000cwp.googlegroups.com>
Content-Type: text/plain; format=flowed

Jimmy ,

The fact that you are creating so darn many macro variables would have me
worried enough to go back to the drawing board and come up with a different
approach.  The fact that a large number of these macro vars are global would
ensure that I would rework my approach.   What are you actually doing that
you feel requires so many macro variables?



Toby Dunn

'They say that Spanish is the Language of Love,
I Loved the way it rolled off of her tongue'

'I dont know what she said but I loved the way she said it'






From: Jimmy <widad2020@GMAIL.COM>
Reply-To: Jimmy <widad2020@GMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Proc SQL INTO Separated to create multiple macro variables
Date: Mon, 24 Jul 2006 07:19:22 -0700

Thanks Scott.It worked.


Scott Bass wrote:
 > "Jimmy" <widad2020@gmail.com> wrote in message
 > news:1153593087.022098.308700@p79g2000cwp.googlegroups.com...
 > >I am using Proc SQL into and Separated by clause to create more than
 > > one macro variable based on on a single column and a dynamic where
 > > condition.
 > > The dataset dept1 may contain many depts and groups .
 > >
 > > Here is my code:
 > > ---------------------------------
 > > options symbolgen mlogic;
 > > data dept1;
 > > input dept group;
 > > datalines;
 > > 1 1
 > > 3 1
 > > 2 1
 > > 60 2
 > > 70 2
 > > 80 2
 > > 90 2
 > > 200 3
 > > 300 3
 > > ;
 > > run;
 > >
 > > proc sql noprint;
 > > select count(distinct group)
 > > into :group_count
 > > from dept1;
 > > quit;
 > >
 > > %let group_count=&group_count;
 > > %put &group_count;
 > >
 > > proc sql noprint;
 > > select distinct group
 > > into :group1 - :group&group_count
 > > from dept1;
 > > quit;
 > >
 > > %macro read();
 > > %local i;
 > > %do i=1 %to &group_count;
 > > %let group&i=&&group&i;
 > > proc sql ;
 > > select dept
 > > into :dept_grp&i separated by ','
 > > from dept1 where group=&&group&i;
 > > quit;
 > > %end;
 > > %mend read;
 > > %read();
 > >
 > > %put &dept_grp1 &dept_grp2 &dept_grp3;
 > >
 > > My problem is &dept_grp1 &dept_grp2 &dept_grp3 are not getting created.
 > >
 > > Please help me with your thoughts.
 > >
 > > Thanks.
 > >
 > Hi,
 >
 > Your problem is that, by default, macro variables created inside a macro
are
 > local to the macro.  Alternatively, you can declare the macro variable as
 > global prior to creating it in the macro.
 >
 > Try something like the below and see if it meets your needs:
 >
 > data dept1;
 > input dept group;
 > datalines;
 > 1 1
 > 3 1
 > 2 1
 > 60 2
 > 70 2
 > 80 2
 > 90 2
 > 200 3
 > 300 3
 > ;
 > run;
 >
 > %macro foo;
 > proc sql noprint;
 >    select distinct group into :group1 - :group99999 from dept1;
 >    %global num;
 >    %let num=&sqlobs;
 >    %do i=1 %to &num;
 >       %global dept&i;
 >       select dept into :dept&i separated by " " from dept1 where
 > group=&&group&i;
 >    %end;
 > quit;
 > %mend;
 > %foo;
 > %put *&dept1* #&dept2# ^&dept3^ &num;
 >
 > HTH,
 > Scott