LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2010, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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? >> >> 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 <> >>>>To: Kevin Y <> >>>>Cc: >>>>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 <> 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 >>>>>; >>>>> >>>>> >>>>> >>>>>

Back to: Top of message | Previous page | Main SAS-L page