Date:         Sat, 15 Feb 2003 21:24:22 -0500
Reply-To:     lpogoda <lpogodajr292185@COMCAST.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         lpogoda <lpogodajr292185@COMCAST.NET>
Subject:      Re: Re-ordering the positions of columns

John Whittington wrote in message
<5.1.0.14.2.20030215030543.04644870@pop3.powernet.co.uk>...
>At 21:46 10/02/03 -0500, lpogoda wrote (in part)
>
>> >Data new ;
>> >   Retain &orderedVars ;
>> >   Set test ;
>> >Run ;
>>
>>In version 8, I've run into at least one case where using RETAIN this way
>>does not reorder the variables in the resulting dataset.
>
>As someone whose name seems to have somehow 'become associated with' this
>method of re-ordering, I'd be very interested if you could expand a little
>on this case in which it doesn't work in v8.  Thanks.
>
Sure.  I work for a small full-service CRO in Philadelphia.  Been there
about three years.  The guy who had my position before me was macro happy -
one of those people who, when you came to him with a problem, would call up
SAS, type "%macro" on the first line of the program window, then ask you
what the problem was.  Over the years, he wrote a ton of macros with the aim
of reducing the time it took to write some more or less standard, recurring
processing task, all in version 6.

Anyway, the purpose of one of these macros was to generate a listing - to
take a SAS dataset, figure out the optimum print length of each variable,
generate and execute PROC REPORT code that would produce the listing.  And
since in these listings the order the variables appeared in was important,
one of the parameters fed to the macro was a list of variables in the
desired display order.

For my money, it was probably the single most useful macro he wrote there,
you could generate all the listings for a study in a day or less.  You fed
it the name of the dataset, the list of variables, the location of the
output, and there was your listing, every time.

Only the first time I tried it under version 8, it didn't work.  Oh, you got
a listing of the request dataset showing the requested variables, but the
variables weren't in the correct order across the page.  Other programmers
tried it on other machines, using different data sets and alternate
destinations, and it didn't work for them either.  Digging into the guts of
the thing, it turned out that he was controlling the print order by
rewriting the dataset according to the variable order parameter, and doing
it in exactly the way you suggested at the top of this post, with a RETAIN
statement followed by a macro variable.

And when we pulled the macro apart and ran things step by step manually, we
found that even though the macro variable was correctly valued, when the
dataset was rewritten this way, the variable order didn't change.  And so
the listing that was produced showed variables in whatever order they
happened to occur in the dataset rather than in the order requested.

Well, the simplest, quickest, least intrusive thing to do was to chuck the
DATA step and RETAIN business, and replace it with a SELECT clause in PROC
SQL.

We're running under Windows 2000.