Date: Wed, 22 Aug 2007 15:35:20 +0000
Reply-To: toby dunn <tobydunn@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: toby dunn <tobydunn@HOTMAIL.COM>
Subject: Re: Proc SQL into: processing
In-Reply-To: <CA8F89971ADA9F47A6C915BA2397844207B41E5E@MAILBE2.westat.com>
Content-Type: text/plain; format=flowed
Sig ,
>>Trouble begins when programmers write SAS Macros that loop thru elements
>>in
>>these lists.
This is a sign that the Macro's design is deficient and the approach should
be changed. List processing is a handy tool and one that I have found easy
to master. It requires no more than following a few basic rules and it
works out 99.9% of the time the first time. On the otherhand it is one
where the programmer must know what he or she is doing or things go very
badly very quickly.
Lately I have seen people bash Call Execute, List processing, Dictionary
views personally they are all just tools to be used and it is in how well
one uses said tool that envariably makes the difference. Personally I use
them all and promote their uses, it is for me atleast a matter of which tool
is the best one for the job at hand.
Toby Dunn
Two wrongs are only the beginning.
Success always occurs in private and failure in full view.
Experience is something you don't get until just after you need it.
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Proc SQL into: processing
Date: Wed, 22 Aug 2007 11:10:34 -0400
Ron Fehd wrote:
>>> Note: using SQL for this task is deprecated
I agree, but I suspect for different reasons ....
The SAS SQL SELECT INTO operator works well when used to create
references to persistent lists (variables, dataset names, etc.) that
insert text where required in the SAS Program Data Vector. Trouble
begins when programmers write SAS Macros that loop thru elements in
these lists. As I have found all too often, it takes a skilled
programmer and much testing to get these looping programs to work
properly and reliably.
Better alternatives for many programming problems include populating
arrays using a dataset of one attribute (a virtual array) and a DOW
loop, a join of a view of one attribute to base relation (dataset), or a
Data step DO i= &__list loop that iterates thru each element of a SELECT
INTO list. While simple SAS Macros improve programmer productivity, more
complicated SAS Macros tend to be time traps and difficult to make
reliable.
S
-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Fehd, Ronald J. (CDC/CCHIS/NCPHI)
Sent: Wednesday, August 22, 2007 10:17 AM
To: ___SAS-L
Subject: RE: Proc SQL into: processing
> From: jofo
> I am stuck on a proc sql processing issue.
> Here is what I am trying to do:
>
> 1. Grab memname of datasets in WORK and put into varlist 2. Put
> varlist, so I know the results are correct 3. Loop through varlist
> and pass to a macro
Note: using SQL for this task is deprecated
You'll want to read these papers which show sql code for your task:
sgf2007.028:
Journeyman's Tools: Data Review Macro FREQALL -
Using PROC SQL List Processing with Dictionary.Columns
to Eliminate Macro DO Loops
Ronald J. Fehd,
http://www2.sas.com/proceedings/forum2007/028-2007.pdf
sgf2007.113:
List Processing Basics:
Creating and Using Lists of Macro Variables
Ronald J. Fehd and Art Carpenter
http://www2.sas.com/proceedings/forum2007/113-2007.pdf
for examples of using call execute to perform this task see:
http://www.sascommunity.org/wiki/Category:Summarize_Memnames_in_Libname
http://www.sascommunity.org/wiki/Image:Summarize-Memnames-in-Libname.zip
Ron Fehd the macro maven CDC Atlanta GA USA RJF2 at cdc dot gov
_________________________________________________________________
More photos, more messages, more storage—get 2GB with Windows Live Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_2G_0507
