Date:         Wed, 29 Aug 2001 13:30:59 -0400
Reply-To:     Edward Heaton <HEATONE@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Edward Heaton <HEATONE@WESTAT.COM>
Subject:      Re: Q to improve code using Macros (correction)
Comments: To: RAHUL CHAHAL <rahulchahal@yahoo.com>
Content-Type: multipart/mixed;

Rahul;
   The SQL code will create your list of input files.  Remember that I
created a file of data set names.  That file is included and is called
mergeDataSetsExample.txt.  I imported the list of data sets into a table
called dataSets using a DATA step.  The code, along with code to create some
test data, is included in mergeDataSetsExample.sas.
   Now, for the SQL task.  If you remove the NOPRINT option from the PROC
SQL statement, you will see that it generates a list that looks like the
following.

        af1 ( in=inSet01)
        af2 ( in=inSet02)
        af3 ( in=inSet03)
        af4 ( in=inSet04)
        zebra ( in=inSet05)
        george ( in=inSet06)

Try it, you'll see.  This is the list that I want in my MERGE statement.
   PROC SQL has two really neat features, the "INTO :" and "SEPARATED BY"
phrases.  Here's how they work.  The INTO says to put the character string
that I am created into a macro variable (The colon specifies a macro
variable.).  Without the SEPARATED BY, the SELECT statement would write
"af1 ( in=inSet01)" into that macro variable, then overwrite it with
"af2 ( in=inSet02)", etc.  The SEPARATED BY phrase tells SAS to concatinate
each of the sections it builds onto what was already built, separating each
segment with a blank space.  Try the following.

        Data test ;
           Do i=1 to 10 ;
              Output ;
           End ;
        Run ;
        Proc sql ;
           Select i into :i separated by "," from test ;
        Run ;
        %Put &i ;

You will get

        1,2,3,4,5,6,7,8,9,10

written to the log.
   Now, I think I've covered everything but the monoTonic function.  It's
the SQL version of the DATA step's _N_, I believe.  At any rate, it seems to
work the same everywhere I've used it.

Hope this helps some,
Ed

Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195
Voice:  (301) 610-4818                      Fax:  (301) 294-3992
mailto:EdwardHeaton@westat.com              http://www.westat.com



-----Original Message-----
From: RAHUL CHAHAL [mailto:rahulchahal@yahoo.com]
Sent: Wednesday, August 29, 2001 10:10 AM
To: Edward Heaton
Subject: Re: Q to improve code using Macros (correction)


Thanks Edward your efforts are appreciated by me.
I liked the solution posted by Dennis yesterday.

If I could ask you to take a few min to explain the
proc sql portion of the code.  Excuse me for lack of
understanding of that part.
Thanks again.

-Rahul

--- Edward Heaton <HEATONE@WESTAT.COM> wrote:
>         Proc sql noPrint ;
>            Select
>               trim( DataSetName )
>                  || " ( in=inSet"
>                  || put( monoTonic() , z2. )
>                  || ")"
>                  into :mergeDataSets
>                  separated by " "
>               from dataSets
>            ;
>         Quit ;


__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/


af1
af2
af3
af4
zebra
george

mergeDataSetsExample.sas [application/octet-stream]