Date: Mon, 11 Oct 2010 12:47:31 -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
In-Reply-To: <201010111618.o9BArQWw025344@malibu.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"
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
>>>;
>>>
>>>
>>>
>>>
| 
