Date:         Tue, 28 Aug 2007 12:51:23 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: sql macro var creation

On Mon, 27 Aug 2007 19:12:44 -0400, Hari Nath <hari_s_nath@YAHOO.COM> wrote:

>Hi all,
>I have created a macro variable "values" here which would give me like
>>>>>val = BOLSNO1","BOLSNO2","CORRNO1","CORRNO2<<<<
>but i am trying to create macro variables something like
>
>value1=BOLSNO1","BOLSNO2
>value2=CORRNO1","CORRNO2
>.....
>
>i could not think of an easy way....could sas-l people help me with
>this.....
>
>many thanks
>hari
>
>>>>>>>>>>>>>>>>>>>>>code<<<<<<<<<<<<<<<<<<<<
>data test ;
> input name $ actevent ;
> cards ;
>CORRNO1  12
>CORRNO2  13
>CORRNO1  12
>CORRNO2  13
>BOLSNO1  23
>BOLSNO2  21
>BOLSNO1  25
>BOLSNO2  28
>;
>run ;
>
>proc sql noprint;
>    select distinct name
>     into   : values separated by '","'
>    from test ;
>   quit;
>%put val = &values ;

I don't see a way to do this entirely in SQL. You would need to vombine a
GROUP BY clause with the INTO clause and to specify both SEPARATED BY
(within a group) and THROUGH (for multiple groups). That's not supported.

Here's a solution combining SQL and DATA step:

   proc sql;
   create view halfbaked as
    select distinct substr(name,1,5) as stem, name from test
    order by name;
   quit;

   data _null_;
   length string $ 999;
   do until (last.stem);
      set halfbaked;
      by stem;
      string = catx( ',' , string , quote(trim(name) ) );
      end;
   index + 1;
   call symput(cats('value',index),string);
   run;

   %put &value1;
   %put &value2;