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.