Date:         Mon, 11 Oct 2010 16:27:21 -0400
Reply-To:     Suzanne McCoy <suzanne.mccoy@CATALINAMARKETING.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Suzanne McCoy <suzanne.mccoy@CATALINAMARKETING.COM>
Subject:      Re: SQL and proc contents

I wasn't able to reproduce the behavior in 9.2 but haven't got time to
finish up the testing today.  I'll finish testing and also test on 9.1.3
if I can.

On Mon, 11 Oct 2010 15:12:05 -0400, Suzanne McCoy
<Suzanne.McCoy@CATALINAMARKETING.COM> wrote:

>Mystery solved.  It was the where xyz in ("macro list") that hit the 2000
character limitation not the macro variable itself.
>
>-----Original Message-----
>From: Arthur Tabachneck [mailto:art297@NETSCAPE.NET]
>Sent: Monday, October 11, 2010 2:46 PM
>To: SAS-L@LISTSERV.UGA.EDU; Suzanne McCoy
>Subject: Re: SQL and proc contents
>
>Suzanne,
>
>Interesting as Richard's proposed test does produce a macro variable using
>that method (i.e., select into separated by).  Has anyone else confronted
>the 2000 character limit that Suzanne mentioned?
>
>When I run Richard's test the macro variable is successfully created up
>until it exceeds the 2^16-2 length.
>
>Art
>---------
>On Mon, 11 Oct 2010 12:47:31 -0400, Suzanne McCoy
><Suzanne.McCoy@CATALINAMARKETING.COM> wrote:
>
>>It was in 9.1.3 on Solaris that bit the hardest and took me hours to
>debug.  It's not a limitation, it's the default length of the macro
>variable when you use a 'select into separated by'.  It didn't fail, it
>just quit concatenating values at 2000.
>>
>>-----Original Message-----
>>From: Arthur Tabachneck [mailto:art297@NETSCAPE.NET]
>>Sent: Monday, October 11, 2010 12:19 PM
>>To: SAS-L@LISTSERV.UGA.EDU; Suzanne McCoy
>>Subject: Re: SQL and proc contents
>>
>>Suzanne,
>>
>>I found and ran the test suggested by Richard, back in 2005, and I don't
>>find that limit on SAS 9.1.3 (m3) on a Windows 2003 server.  Does it fail
>>at 2000 characters on your system and, if so, which version of SAS are
you
>>using and on which operating system?
>>
>>http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0505b&L=sas-
l&D=1&O=A&P=14026
>>
>>Art
>>--------
>>On Mon, 11 Oct 2010 11:17:30 -0400, Suzanne McCoy
>><Suzanne.McCoy@CATALINAMARKETING.COM> wrote:
>>
>>>If you don't control the length it defaults to 2000 max.  I've never
>>looked for it in the documentation but have had it bite me.  It's pretty
>>easy to debug if the truncation causes an error but if it truncates and
>>doesn't cause errors it may take a while to notice that there is a
>problem.
>>>
>>>-----Original Message-----
>>>From: Arthur Tabachneck [mailto:art297@NETSCAPE.NET]
>>>Sent: Monday, October 11, 2010 10:45 AM
>>>To: SAS-L@LISTSERV.UGA.EDU; Suzanne McCoy
>>>Subject: Re: SQL and proc contents
>>>
>>>Suzanne,
>>>
>>>Is that a restriction I'm just not aware of?  I thought that the limit
>for
>>>macro variable size was 2^16-2.
>>>
>>>Art
>>>--------
>>>On Mon, 11 Oct 2010 10:19:35 -0400, Suzanne McCoy
>>><Suzanne.McCoy@CATALINAMARKETING.COM> wrote:
>>>
>>>>Just make sure the string doesn't go longer than 2000 characters or it
>>>will truncate and you may not notice it.
>>>>
>>>>-----Original Message-----
>>>>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>>>Kevin Y
>>>>Sent: Monday, October 11, 2010 10:09 AM
>>>>To: SAS-L@LISTSERV.UGA.EDU
>>>>Subject: Re: SQL and proc contents
>>>>
>>>>Joe:
>>>>
>>>>You absolutely correct.
>>>>
>>>>It is much better to use one single macro variable to do the work.
>>>>
>>>>Thanks for your answer and suggestion.
>>>>
>>>>
>>>>
>>>>
>>>>________________________________
>>>>From: Joe Matise <snoopy369@gmail.com>
>>>>To: Kevin Y <kevin77711@yahoo.com>
>>>>Cc: SAS-L@listserv.uga.edu
>>>>Sent: Mon, October 11, 2010 9:54:05 AM
>>>>Subject: Re: SQL and proc contents
>>>>
>>>>Yep.  see dictionary.tables, dictionary.columns, etc.
DICTIONARY.TABLES
>>>has the
>>>>table names in it, DICTIONARY.COLUMNS has the variable names from each
>>>table.
>>>>If you use dictionary.tables you shouldn't need the DISTINCT qualifier.
>>>>
>>>>proc sql;
>>>>select memname into whatever from dictionary.tables where
libname='LIB';
>>>>quit;
>>>>
>>>>Why are you making so many variables though?  90% of the time it's
>better
>>>to
>>>>make a single macro variable list:
>>>>
>>>>select distinct memname into :whatever separated by ' ' from
>>>dictionary.tables
>>>>where libname='LIB';
>>>>
>>>>for example.
>>>>
>>>>-Joe
>>>>
>>>>
>>>>On Mon, Oct 11, 2010 at 8:41 AM, Kevin Y <kevin77711@yahoo.com> wrote:
>>>>
>>>>I always do the following in two steps. Can we do the first step using
>>>SQL too,
>>>>>please?
>>>>>
>>>>>proc contents data=lib._all_ out=all noprint;
>>>>>run;
>>>>>
>>>>>proc sql;
>>>>>select distinct memname into: no1 - :no&sysmaxlong
>>>>>from all
>>>>>;
>>>>>
>>>>>
>>>>>
>>>>>