Date:         Mon, 11 Oct 2010 10:19:35 -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
Comments: To: Kevin Y <kevin77711@YAHOO.COM>
In-Reply-To:  <93477.45822.qm@web59616.mail.ac4.yahoo.com>
Content-Type: text/plain; charset="iso-8859-1"

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
>;
>
>
>
>