Date:         Tue, 24 Sep 2002 15:25:35 -0400
Reply-To:     Howard_Schreier@ITA.DOC.GOV
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard_Schreier@ITA.DOC.GOV
Subject:      Re: Why not use Proc SQL to rearrange variables?

I think you are right in considering SQL to be a good tool for re-ordering.

I used to be concerned about the inheritance of variable attributes (label,
format, etc.), but limited experiments suggest that SQL seems to behave as
one would wish in that regard.

You raised the issue of trying to do too many things in a DATA step and
getting unintended interactions as a result. Views are a useful device for
separating processes into "layers" which are pretty well insulated from one
another.

On Tue, 24 Sep 2002 12:56:29 -0400, Edney.Shawn@EPAMAIL.EPA.GOV wrote:

>I wanted to get some feedback on why the preferred methods of
>rearranging variables in a SAS data set are to use the length or retain
>statement. I feel as though I am a master at rearranging variables(what
>a skill!) and I am quite competent at planning ahead so that I create or
>initialize variables in the right order as I go along.
>Often when I do need to rearrange variables I also need to manipulate
>the data set further such as subsetting the data etc. So I will then
>perform my variable rearrangement within the manipulation step so that I
>won't have to execute another data step just for rearrangement purposes.
>My worry in this area is that the length and retain statements can both
>cause unexpected changes in your data if one just sticks them in without
>thinking things through. I know that not thinking is a problem in many
>areas but I can't help but wonder why something such as proc sql  seems
>to never be mentioned(did I miss it?) as a solution to this problem.
>Take a data set work.data with columns var1-var5 then rearrange them
>var3, var2, var5,var1,var4 with the code
>
>proc sql;
>create table work.data as
>select
>var3,
>var2,
>var5,
>var1,
>var4
>from work.data;
>
>
>This seems to be much safer solution. If a poster is inexperienced
>enough (I know I once was ) to not know how to rearrange variables maybe
>they will improperly use the length and retain statements(retain seems
>especially dangerous). I am not saying that the other methods are being
>inappropriately recommended, as I generally use them for rearrangement
>of vars but I wanted to know if there was some drawback to the sql
>solution. Is there some great performance loss? Is this method just not
>widely known? Does is suffer under some kind of stigma?
>
>If I have a really wide data set that includes cryptic or long variable
>names and I need just a subset of the variables, say 20, in a particular
>order I will sometimes just use the sql query window to pick them out in
>the order needed then save the code and append it into my main program.
>Viola, a bug free procedure. It makes typos of long cryptic vars
>impossible. What would be the drawbacks to this?
>
>
>Just curious ...
>
>Thanks for your feedback,
>
>
>Shawn Edney
>
>Analyst/Programmer
>Dyncorp Systems and Solutions, LLC.
>79 T.W. Alexander Drive
>Bldg. 4201 Suite 201
>Research Triangle Park, NC
>27709
>
>(919) 558-8782 ext 216
>fax  558-8788
>edney.shawn@epa.gov