What is the best way to change column order when adding new columns?

[musicandyceram]

In SAS Studio 9.4v2:

data np_summary_update;

set pg1.np_summary;

keep Reg ParkName SqMiles Camping DayVisits OtherLodging Acres ;

SqMiles = acres * .0015625; 

Format SqMiles comma6.;

Camping = sum (OtherCamping, TentCampers, RVCampers, BackCountryCampers);

Format Camping comma11.;

Retain  Reg ParkName Acres SqMiles Camping DayVisits OtherLodging ;

set pg1.np_summary;

data want;

run;

Output produces columns in this order:  Reg ParkName  DayVisits OtherLodging Acres SqMiles Camping.

This is my code from a SAS Programming 1 exercise. I understood the initial assignment and solved it. I then started futzing around looking for a way to move around the newly created columns in the KEEP statement. I have tried a few things which didn't work. The code above is just the last stab at it. Suggestions?  

Also, code with an explanation why would be deeply appreciated.

Thank you. 

[PaigeMiller]

Basically, column position makes no difference to SAS. Anything you want to do in SAS works regardless of order of the columns in the data set (although there might be some rare exceptions).

The only time column position makes a difference is when you are creating an output that needs to be viewed by humans and then a logical order of columns is needed in the output, not in the SAS data set. So with that in mind, the columns positions can be easily specified in output PROCs, such as PRINT, TABULATE and REPORT. Since you asked for the "best way", I believe this to be the "best way".

Thus my advice is to not bother re-arranging the columns in a data set. Re-arranging the columns when you output the results is the time to do it.

[Tom]

I don't know about you but I actually like to LOOK at my data.  That is very difficult when the variables are placed randomly.  There is nothing worse than trying to debug something and looking at a dataset here the whole first screen is full of long empty character variables of low importance.

[data_null__]

Create a template dataset.

title "Template data to reorder variables";
data template;
   attrib Trt    length=8  label='Treatment';
   attrib Sex    length=$1 label='Gender';
   attrib Age    length=8  label='Age (years)';
   attrib Weight length=8;
   attrib Height length=8;
   attrib Name   length=$10 format=$quote12.;
   stop;
   call missing(of _all_);
   run;

data class;
   set template sashelp.class;
   trt = rantbl(12345,.6)-1;
   run;
proc contents varnum;
   run;
proc print;
   run;

proc contents data=sashelp.class varnum;
   run;

[sbxkoenk]

PROC SQL ?

See 

SESUG Paper 242-2018

Order, Order! Four Ways To Reorder Your Variables, Ranked by Elegance and Efficiency

Louise S. Hadden, Abt Associates Inc.

https://www.lexjansen.com/sesug/2018/SESUG2018_Paper-242_Final_PDF.pdf

BR, Koen

[Tom]

The data step compiler adds the variables to the dataset in the order.  So to change the order make sure the code references the variables in the order you want. 

Not all references actually cause SAS to need to place the variable in the dataset at that time. For example the DROP or KEEP statement does not impact order (even if it is the first place the variable is referenced).  Instead they just set flags to indicate if the variable will be written to the output dataset.

The data step compiler will also set the type (and storage length) of the variable at the first place where it HAS to.  But not all references will set the type.  For example the RETAIN statement will set the variables place, but it will not determine its TYPE.  That will be (or has been) determined by where it is used. 

So you can use RETAIN to set the order.  Just be sure that your data step will not be impacted by the actual purpose of a RETAIN statement, that the variables value is not clear when a new iteration starts.

Example:

 73         data _null_;
 74           keep height age name ;
 75           set sashelp.class(obs=1);
 76           put (_all_) (=/);
 77         run;
 
 Name=Alfred
 Sex=M
 Age=14
 Height=69
 Weight=112.5
 NOTE: There were 1 observations read from the data set SASHELP.CLASS.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 78         
 79         data _null_;
 80           retain height age name ;
 81           set sashelp.class(obs=1);
 82           put (_all_) (=/);
 83         run;
 
 height=69
 age=14
 name=Alfred
 Sex=M
 Weight=112.5
 NOTE: There were 1 observations read from the data set SASHELP.CLASS.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

Notice that the RETAIN statement also set the case of the variable names and the KEEP statement did not.

To really set the order you should probably just DEFINE all of the variables first using a LENGTH statement.

data want;
  length age height 8 Sex $1 Name $10 weight 8;
  set sashelp.class(obs=1);
run;

proc contents varnum;
run;

But if you don't know how to define the existing variable then perhaps you could just use a series of references to the dataset to let the data step compiler find it for you.  Say you want to move AGE to the front and leave the others in place.

data want;
  set sashelp.class(keep=age);
  set sashelp.class;
run;