Date:         Fri, 16 Jan 1998 19:08:01 -0500
Reply-To:     "Paul M. Dorfman" <pdorfma@UCS.ATT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Paul M. Dorfman" <pdorfma@UCS.ATT.COM>
Organization: AT&T UCS
Subject:      Re: Procs in a loop
Content-Type: text/plain; charset=us-ascii

Michael Rose wrote:
>
> I was wondering how to get a PROC inside a loop or a data step. I need
> to call a series of SAS PROCS -- SQL and IML and pass data to them
> based on the data in the current record of a data step. For example,
>
> DATA X;
>
>    KeyIWant = Key;
>    PROC SQL;
>    CREATE TABLE JUNK AS
>    SELECT * FROM STUFF WHERE KEY = KeyIWant;
>
>    Etc.
>
> Any ideas.
 
Michael,
 
Rule number one: a proc (and/or DATA step) can be executed
conditionally or repeatedly in a loop from within a macro
but not from within (another) DATA step.
 
To achieve what you seem to be trying to do in your example,
i.e. create a SAS dataset JUNK, there is no need to use
PROC SQL; mere
 
DATA JUNK; SET STUFF; WHERE KEY=KEYIWANT; RUN;
 
would suffice. However, if "etc." means other procs (or DATA
steps), go to rule number one.
 
Other procs aside, passing data from a current record of an
external file or observation of a SAS dataset to PROC SQL
would be a greatly coveted thing to have, especially in the
case of handling external databases like DB2. Unfortunately,
embedded SQL has not yet been implemented, and even though
it puts SAS into serious disadvantage next to other languages
like Cobol, I'm not sure whether it's being planned for future
releases. In lieu of embedded SQL, somewhat artificial means
of working with driver files based on macro language have been
used, for instance, running a single query with keys from many
driver file records (but no more than the query can handle) at
once, then appending the result table to one created before and
so on.
 
I'd be glad to see this "embedded SQL" thread go on, so I'm
looking forward eagerly to any thoughts on this subject.
 
---------------------------------
Paul M. Dorfman
AT&T UCS Decision Support Systems
Jacksonville, Fl
---------------------------------