Date:         Fri, 17 Dec 2004 00:45:32 -0500
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 Question

Any SQL solution is going to be verbose and awkward because SQL does not
have a mechanism for arrays (because SQL is designed to work with
normalized data).

If you use a DATA step instead of SQL, you do not need a separate PROC
PRINT to display the result. Instead, do something like this:

   data _null_;
   array x{*} x1-x10;
   do i=1 to dim(x); x{i}=i; end;
   mush = catx (' ', of x1-x10);
   file print;
   put mush;
   run;

On Thu, 16 Dec 2004 12:39:25 -0500, Richard A. DeVenezia
<radevenz@IX.NETCOM.COM> wrote:

>Scott wrote:
>> Hi,
>>
>> I want to print the concatenation of a bunch of numeric variables
>> using SQL.
>>
>> Test Data:
>>
>> data one;
>>    length x1-x10 8;
>>    array x{*} x1-x10;
>>    do i=1 to dim(x);
>>       x{i}=i;
>>    end;
>> run;
>>
>> Desired Output:
>>
>>    i   x
>> ---------------------------
>>   11   1 2 3 4 5 6 7 8 9 10
>>
>> Can this be easily done in SQL, perhaps with some sort of nested
>> query?
>
>* version 9 catx() function works like a charm;
>* however, data step array syntax [of...] is not understood by SQL :( ;
>
>data one;
>   length x1-x10 8;
>   array x{*} x1-x10;
>   do i=1 to dim(x);
>      x{i}=i;
>   end;
>   mush = catx (' ', of x1-x10);
>run;
>
>proc sql;
>  create table two as
>  select i, mush, catx (' ', x1,x2,x3,x4,x5,x6,x7,x8,x9,x10) as dude
>  from one
>  ;
>quit;
>
>
>--
>Richard A. DeVenezia
>http://www.devenezia.com/