Order, Order! Seven Ways to Reorder Variables in a SAS Data Set and Resulting Output

[#1]

SAS® practitioners are frequently required to present variables in an output data file in a particular order, or standards may require variables in a production data file to be in a particular order. Common reasons for wanting to reorder variables in a data file are the result of merging files (order will be common variables first, then variables in the original order of each merged file) or the use of ARRAY, ATTRIB, LENGTH or RETAIN statements in originating data files that “disordered” variables.

SAS provides us with numerous methods to control all types of SAS output, including SAS data files, data tables in other formats, and ODS output. This article focuses solely on output SAS data files (which may then be used to generate SAS data files and other types of output from SAS processes) created from other SAS data sets, and specifically on DATA step and PROC SQL methods.

First, Know Thy Data

It is always important to understand fully and explore the inputs to SAS-created output. In order to reorder variables, you first must gather information about those variables, including their current location in a SAS data set. This metadata can be obtained in any number of ways, including  PROC CONTENTS, PROC DATASETS, dictionary tables, SASHELP views, %sysfunc and other system macros, and V functions. For the purposes of illustration, we'll use the ubiquitous PROC CONTENTS here. Code to create the Hogwartian data set is included below at the end of the article.

* THE CONTENTS PROCEDURE;
PROC CONTENTS DATA=DD.HARRYPOTTER_LABELLED
    OUT=LABELLED_CONTENTS
    (KEEP=NAME TYPE LENGTH LABEL FORMAT INFORMAT VARNUM);
RUN;

An output data file is created from the CONTENTS procedure, with a KEEP statement that retains only the variables of interest.

If we wish to change the order in which variables are presented in an existing data file, we need to know how SAS determines variable order under various conditions. In the DATA step, variable order is determined by the first mention of the variable as it passes through the Program Data Vector (PDV.) DATA step statements that can set the order of variables include ARRAY, ATTRIB, FORMAT, INFORMAT, LENGTH and RETAIN. These statements MUST precede any SET, MERGE or UPDATE statements so as to accomplish the goal of reordering variables. The order in which variables are mentioned in the statement determines the order of the variables in the corresponding data file. If variables are not listed in the statement, variables that are mentioned will appear in the order that they are mentioned, remaining variables will appear in their original order in the originating data file (or INPUT statements in the case of data files being created).

In addition to DATA step statements, PROC SQL can also be utilized to reorder variables in an existing data file, in a similar fashion to the DATA STEP statements described above. PROC SQL SELECT and SELECT AS statements may be used to order, format, set the length of and rename variables in a new data file created from the existing data file.

Each method of reordering data requires the provision of metadata information in the statement. Rather than type in what could be a very extensive variable attribute list, SAS metadata can be used to build the statements. Some methods (RETAIN, FORMAT, INFORMAT, PROC SQL) require an ordered list of variables as input (note that additional metadata information can be used, but is not strictly necessary.) Others (ATTRIB, ARRAY, LENGTH) require additional information, such as the length of a variable, variable label, and/or type of variable. SAS metadata can be used to construct the statements in a variety of ways. Two methods of harnessing SAS metadata to build data-driven code follow. The precursor to these steps is the use of the LABELLED_CONTENTS data file created above, and sorted in various ways into different output data files.

* TEST FILE PRECURSOR STEPS: SORT OUT DATA SETS IN SPECIFIC ORDERS;
* VARIABLE NAME (NAME) IS KEY;
* POINT IS TO PROVIDE VARIABLE LISTS IN DIFFERENT ORDER;

*ALPHA ORDER;
PROC SORT DATA=LABELLED_CONTENTS OUT=LABELLED_BY_NAME;
    BY NAME;
RUN;

*DESCENDING POSITION;
PROC SORT DATA=LABELLED_CONTENTS OUT=LABELLED_BY_DVARNUM;
    BY DESCENDING VARNUM;
RUN;

*LENGTH ORDER;
PROC SORT DATA=LABELLED_CONTENTS OUT=LABELLED_BY_LENGTH;
    BY LENGTH;
RUN;

* TEST FILE PREPARATION METHOD 1: MACRO LISTS FROM METADATA VIA PROC SQL INTO:;
* CREATES MACRO LISTS THAT CAN BE USED IN DATA SET STATEMENTS AND PROC SQL;

PROC SQL;
     SELECT NAME
     INTO :VARNUM_ORDER SEPARATED BY ' '
     FROM LABELLED_CONTENTS;
quit;

%PUT By Original Order = &VARNUM_ORDER;

PROC SQL;
     SELECT NAME
     INTO :NAME_ORDER SEPARATED BY ' '
     FROM LABELLED_BY_NAME;
quit;

%PUT By Alpha Order = &NAME_ORDER;

* TEST FILE PREPARATION METHOD 2: STRUCTURED INCLUDE FILE VIA DATA _NULL_;
* CREATES AN “INCLUDE” FILE TO BE USED IN LENGTH STATEMENTS USING SAS METADATA;
* NOTE CONDITIONALLY CREATES STRINGS BASED ON TYPE;

DATA _NULL_;
     FILE '.\STRING_ORDER.TXT' LRECL=50 PAD;
     LENGTH STRING_ORDER $ 50;
     SET LABELLED_BY_NAME;
     IF TYPE=2 THEN STRING_ORDER=CATX(' ',NAME,'$',LENGTH);
     ELSE IF TYPE=1 THEN STRING_ORDER=CATX(' ',NAME,LENGTH);
     PUT STRING_ORDER;
RUN;

* TEST FILE PREPARATION METHOD 3: STRUCTURED INCLUDE FILE VIA DATA _NULL_;
* CREATES AN “INCLUDE” FILE TO BE USED IN ARRAY STATEMENTS USING SAS METADATA;
* NOTE CONDITIONALLY CREATES SEPARATE MACRO VARIABLES BASED ON TYPE;

DATA CHARS NUMS;
    SET LABELLED_BY_DVARNUM;
    IF TYPE=2 THEN OUTPUT CHARS;
    ELSE IF TYPE=1 THEN OUTPUT NUMS;
RUN;

PROC SQL;
     SELECT NAME
     INTO :DVARNUM_ORDER_C SEPARATED BY ' '
     FROM CHARS;
QUIT;

PROC SQL;
     SELECT NAME
     INTO :DVARNUM_ORDER_N SEPARATED BY ' '
     FROM NUMS;
quit;

%PUT By Descending Varnum Char Order = &DVARNUM_ORDER_C;
%PUT By Descending Varnum Num Order = &DVARNUM_ORDER_N;

An example of using the LENGTH statement (created as a %INCLUDE file in test file preparation method 2) is shown below. The LENGTH statement requires that a type designation (for character variables) and length be provided, thus SAS metadata was used in the creation of the %INCLUDE file above. Note that the LENGTH statement is above the set statement. The ATTRIB statement can be constructed in a similar fashion as the LENGTH statement using SAS metadata, and employed in the same manner.

* EXAMPLE 1: LENGTH STATEMENT (ATTRIB STATEMENT IS SIMILAR);
DATA DD.HARRYPOTTER_LENGTH
    (LABEL="Harry Potter Sample Data Set - Variables Ordered with Length Statement");
     LENGTH
     %INCLUDE '.\STRING_ORDER.TXT';
     SET DD.HARRYPOTTER_LABELLED;
RUN;

Using ARRAY statements created by using SAS metadata is similar to the LENGTH statement. Separate macro variable lists of character and numeric variables are created based on variable type in the metadata and deployed in the DATA step, again above the SET statement. The reason for separating character and numeric variables is because arrays do not allow mixing of variable types. Additional instructions can be put on the ARRAY statements such as format information, and depending on the metadata values for type and format, you could have multiple ARRAY statements. In this example, no information beyond type is utilized.

* EXAMPLE 2: ARRAY STATEMENT;
DATA DD.HARRYPOTTER_ARRAY
    (LABEL="Harry Potter Sample Data Set - Variables Ordered with Array
    Statements");
    ARRAY CH (*) $ 50 &DVARNUM_ORDER_C. ;
    ARRAY NU (*) &DVARNUM_ORDER_N. ;
    SET DD.HARRYPOTTER_LABELLED;
RUN;

The RETAIN statement is similar to the ARRAY statement in that variable metadata information is not required to reorder variables, just a list of the variables in the appropriate order. The FORMAT and INFORMAT statements behave in the same fashion. All three of these DATA STEP statements can perform additional operations, but in this context, they are not necessary and can complicate the task. For example, RETAIN is used to retain, or carry values across iterations of a DATA step. If RETAIN is used for this purpose, AND to reorder the data, there’s a risk of errors and/or unexpected results. The ability of these statements to reorder variables is simply a by-product of how they operate.

* EXAMPLE 3: RETAIN / FORMAT / INFORMAT STATEMENTS;
DATA DD.HARRYPOTTER_RETAIN
    (LABEL="Harry Potter Sample Data Set - Variables Ordered with Retain Statement");
    RETAIN &NAME_ORDER.;
    SET DD.HARRYPOTTER_LABELLED;
RUN;

PROC SQL in the context of variable reordering performs in a similar fashion to the RETAIN / FORMAT / INFORMAT statements in that PROC SQL only REQUIRES a reordered list of variables, but allows the specification of additional attributes, such as type, length, format, etc. PROC SQL is also unique in that it allows you to rename or “alias” variables. Often, SAS practitioners wish to reorder variables in a data file to make reporting more straightforward. However, there are circumstances that require that output data (temporary or permanent SAS data files, Excel files, XML files, etc.) be ordered in a certain way. For example for XML files that are created using XML maps or schemas, case (upper, lower, proper or camelcase), matters as well as variable order. PROC SQL allows for the renaming of variables to match XML case requirements in the reordering process. Although the code for this example was not built programmatically, as with the other three examples, it could easily be.

* EXAMPLE 4: PROC SQL TO REORDER VARIABLES;
PROC SQL;
    CREATE TABLE DD.HARRYPOTTER_SQL AS SELECT
    animal                                      
    ,bloodstatus                                 
    ,combined_name                               
    ,da                                             
    ,deatheater                                     
    ,deceased as deadasadoornail                                      
    ,firstname                                    
    ,house                                       
    ,lastname                                     
    ,middlename                                    
    ,ministryofmagic as MOM                               
    ,nickname                                     
    ,oop as OrderOfthePhoenix                                           
    ,patronus                                     
    ,professor                                       
    ,quidditch_wins                                  
    ,school  
    from dd.harrypotter_labelled
    ORDER BY lastname ;
QUIT;

The PROC SQL and the ATTRIB statement methods provide the most flexibility and least amount of time spent coding to accomplish the functional outcome of reordering variables. PROC SQL edges out the ATTRIB statement with the ability to rename or “alias” variables within the same procedural statement.

Below follows code to create the sample data file used in the examples above. However, any data file available to the user will work with the techniques described in the paper. SASHELP.CLASS or SASHELP.HEART are frequently used for demonstration purposes.

title1 'Create Harry Potter themed data file';
run;
data dd.harrypotter (label='Harry Potter themed data file for use in demonstrating functions');
     length combined_name school bloodstatus $ 50 firstname middlename lastname
            nickname house patronus animal $ 25 ;
     combined_name='Potter, Harry';
     firstname='Harry';
     middlename='James';
     lastname='Potter';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Stag';
     nickname='';
     animal='Snowy Owl';
     quidditch_wins=9;
     professor=0;
     ministryofmagic=1;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Half-Blood';
     output;

     combined_name='Longbottom, Neville';
     firstname='Neville';
     middlename='';
     lastname='Longbottom';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Non-Corporeal';
     nickname='';
     animal='Toad';
     quidditch_wins=.N;
     professor=1;
     ministryofmagic=0;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Lovegood, Luna';
     firstname='Luna';
     middlename='';
     lastname='Lovegood';
     nickname='Loony';
     school='Hogwarts';
     house='Ravenclaw';
     patronus='Hare';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Weasley, Ronald';
     firstname='Ronald';
     middlename='Bilius';
     lastname='Weasley';
     school='Hogwarts';
     house='Gryffindor';
     nickname='Ron';
     patronus='Jack Russell Terrier';
     animal='Rat, then Owl';
     quidditch_wins=6;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     output;

     combined_name='Weasley, Fred';
     firstname='Fred';
     middlename='Fabian';
     lastname='Weasley';
     school='Hogwarts';
     house='Gryffindor';
     nickname='Gred';
     patronus='';
     animal='';
     quidditch_wins=8;
     professor=0;
     ministryofmagic=0;
     deceased=1;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Weasley, George';
     firstname='George';
     middlename='Gideon';
     lastname='Weasley';
     school='Hogwarts';
     house='Gryffindor';
     nickname='Forge';
     patronus='';
     animal='';
     quidditch_wins=8;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Granger, Hermione';
     firstname='Hermione';
     middlename='Jean';
     lastname='Granger';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Otter';
     nickname='Hermy';
     animal='Cat';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Muggle Born';
     output;

     combined_name='Weasley, Ginevra';
     firstname='Ginevra';
     middlename='Molly';
     lastname='Weasley';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Horse';
     nickname='Ginny';
     animal='Puffskein';
     quidditch_wins=25;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Weasley, Percival';
     firstname='Percival';
     middlename='Ignatius';
     lastname='Weasley';
     school='Hogwarts';
     house='Gryffindor';
     patronus='';
     nickname='Percy';
     animal='Rat, then Owl';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=1;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Malfoy, Draco';
     firstname='Draco';
     middlename='Lucius';
     lastname='Malfoy';
     school='Hogwarts';
     house='Slytherin';
     patronus='';
     nickname='';
     animal='';
     quidditch_wins=2;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=1;
     bloodstatus='Pure Blood';
     output;

     combined_name='Black, Sirius';
     firstname='Sirius';
     middlename='Orion';
     lastname='Black';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Black Dog';
     nickname='Padfoot';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=1;
     bloodstatus='Pure Blood';
     output;

     combined_name='Black, Regulus';
     firstname='Regulus';
     middlename='Arcturus';
     lastname='Black';
     school='Hogwarts';
     house='Slytherin';
     patronus='';
     nickname='RAB';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=0;
     deatheater=1;
     bloodstatus='Pure Blood';
     output;

     combined_name='Dumbledore, Albus';
     firstname='Albus';
     middlename='Percival Wulfric Brian';
     lastname='Dumbledore';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Phoenix';
     nickname='';
     animal='Phoenix';
     quidditch_wins=.N;
     professor=1;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=1;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

     combined_name='Tonks, Nymphadora';
     firstname='Nymphadora';
     middlename='';
     lastname='Tonks';
     school='Hogwarts';
     house='Hufflepuff';
     patronus='Wolf';
     nickname='Tonks';
     animal='';
     professor=0;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=1;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

     combined_name='MacMillan, Ernie';
     firstname='Ernie';
     middlename='';
     lastname='MacMillan';
     school='Hogwarts';
     house='Hufflepuff';
     patronus='Boar';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=1;
     oop=0;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

     combined_name='Dumbledore, Aberforth';
     firstname='Aberforth';
     middlename='';
     lastname='Dumbledore';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Goat';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

 

     combined_name='Potter, James';
     firstname='James';
     middlename='';
     lastname='Potter';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Stag';
     nickname='Prongs';
     animal='';
     quidditch_wins=9;
     professor=0;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Potter, Lily';
     firstname='Lily';
     middlename='';
     lastname='Potter';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Doe';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Muggle Born';
     output;

     combined_name='Snape, Severus';
     firstname='Severus';
     middlename='';
     lastname='Snape';
     school='Hogwarts';
     house='Slytherin';
     patronus='Doe';
     nickname='Half-Blood Prince';
     animal='';
     quidditch_wins=.N;
     professor=1;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=1;
     deatheater=1;
     bloodstatus='Half Blood';
     output;    

     combined_name='McGonagall, Minerva';
     firstname='Minerva';
     middlename='';
     lastname='McGonagall';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Cat';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=1;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

     combined_name='Lupin, Remus';
     firstname='Remus';
     middlename='';
     lastname='Lupin';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Moon';
     nickname='Moony';
     animal='';
     quidditch_wins=.N;
     professor=1;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=1;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

     combined_name='Weasley, Arthur';
     firstname='Arthur';
     middlename='';
     lastname='Weasley';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Weasel';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=1;
     deceased=0;
     da=0;
     oop=1;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Shacklebolt, Kingsley';
     firstname='Kingsley';
     middlename='';
     lastname='Shacklebolt';
     school='Hogwarts';
     house='';
     patronus='Lynx';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=1;
     deceased=0;
     da=0;
     oop=1;
     deatheater=0;
     bloodstatus='Pure Blood';
     output;

     combined_name='Finnigan, Seamus';
     firstname='Seamus';
     middlename='';
     lastname='Finnigan';
     school='Hogwarts';
     house='Gryffindor';
     patronus='Fox';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

     combined_name='Chang, Cho';
     firstname='Cho';
     middlename='';
     lastname='Chang';
     school='Hogwarts';
     house='Hufflepuff';
     patronus='Swan';
     nickname='';
     animal='';
     quidditch_wins=4;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

     combined_name='Delacour, Fleur';
     firstname='Fleur';
     middlename='';
     lastname='Delacour';
     school='Beauxbatons Academy of Magic';
     house='';
     patronus='';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Unknown';
     output;   

     combined_name='Delacour, Gabrielle';
     firstname='Gabrielle';
     middlename='';
     lastname='Delacour';
     school='Beauxbatons Academy of Magic';
     house='';
     patronus='';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Unknown';
     output;

     combined_name='Maxime, Olympe';
     firstname='Olympe';
     middlename='';
     lastname='Maxime';
     school='Beauxbatons Academy of Magic';
     house='';
     patronus='';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=1;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Half Human';
     output;

     combined_name='Hagrid, Rubeus';
     firstname='Rubeus';
     middlename='';
     lastname='Hagrid';
     school='Hogwarts';
     house='Gryffindor';
     patronus='';
     nickname='Hagrid';
     animal='Acromantula';
     quidditch_wins=.N;
     professor=1;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Half Blood';
     output;

     combined_name='Krum, Victor';
     firstname='Victor';
     middlename='';
     lastname='Krum';
     school='Durmstrang Institute';
     house='';
     patronus='';
     nickname='';
     animal='';
     quidditch_wins=85;
     professor=0;
     ministryofmagic=0;
     deceased=0;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Unknown';
     output;

     combined_name='Grindelwald, Gellert';
     firstname='Gellert';
     middlename='';
     lastname='Grindelwald';
     school='Durmstrang Institute';
     house='';
     patronus='';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=0;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=0;
     deatheater=0;
     bloodstatus='Pure or Half Blood';
     output;

     combined_name='Karkaroff, Igor';
     firstname='Igor';
     middlename='';
     lastname='Karkaroff';
     school='Durmstrang Institute';
     house='';
     patronus='';
     nickname='';
     animal='';
     quidditch_wins=.N;
     professor=1;
     ministryofmagic=0;
     deceased=1;
     da=0;
     oop=0;
     deatheater=1;
     bloodstatus='Pure Blood';
     output;

 run;

[ErikLund_Jensen]

In most cases, reordering of variables is only necessary if data is presented in tabular form, as proc print or export to excel, where a specific order is required. This is seldom in alphabetical or reverse ordinal order or similar, that can be achieved through an algotithm, but requires a list of variables in the wanted order.  And If the output data set contains new variables, these should probably be placed in specific positions between existing variables. 

An easy way to solve this without the need of specifying attributes of existing variables is to access input data through an SQL view, where new variables can be inserted as empty in the right positions too:

data work.a;
	a = 1; 
	b = 2; 
	c = 3; 
	d = 4;
run;

proc sql;
	create view v_a as
		select 
			b, 
			'' as f length=30, 
			d, 
			. as e, 
			a, 
			c
	from work.a;
quit;

data b; set v_a;
	e = 333;
	f = 'John';
run;

[PaigeMiller]

reordering of variables is only necessary if data is presented in tabular form, as proc print

Why not just specify the order in PROC PRINT or PROC TABULATE or PROC REPORT?

[ErikLund_Jensen]

@PaigeMiller 

The topic was reordering of variables, and I introduced another way of doing that without giving any opinion on whether it is sensible to do or not, and I just mentioned proc print as an example of a tabular view.

If data is used for making reports, the order could be specified there as well, but it would still be a SAS programmer who reordered the variables for presentation to the user, and it does not really matter at which stage in the process is is done.

But it does matter if there is no report to do the reordering in. What I really had in mind was the (in our organization several hundred) users who access SAS data through the SAS ADD-IN to Microsoft Office. The like having key variables like departments and names visible when they open the table without the need for scrolling.

[PaigeMiller]

And my point is that it is just easier for a programmer to do it in PROC PRINT or PROC REPORT or PROC TABULATE, rather than use these somewhat obscure methods of re-arranging the columns of the data set.

[louisehadden]

The reason for reordering variables in my case was to prepare existing SAS data sets for XML output using an XML map or schema. The PROC SQL method was the best fit because XML schema are case sensitive and variable name case (as well as variable name) can be manipulated via an AS statement in PROC SQL - and of course, one could specify variable order as well. It's true that one can specify order in reporting procedures, and that variable / column order can be specified during the creation process. The paper's premise was based on reordering variables in existing data stores for output data sets. 

[ErikLund_Jensen]

@PaigeMiller 

I do not disagree. Could we reach a compromise saying something like "If it is unnecessary, don't do it. Else do it in the simplest possible way, and if it can be done once instead of being repeated at every usage, then do it once".

[PaigeMiller]

@ErikLund_Jensen that's fine with me

@louisehadden I am not familiar with the requirements to output to XML, so I can't comment further on that. However, the initial problem statement in the first paragraph doesn't mention XML (that is not mentioned until much further down, in the 10th or 11th paragraph), and the new SAS user who comes across this document — which in the title talks about re-ordering columns for output — will think that to re-order columns, there are these complicated programming methods that need to be followed, when in most situations the VAR statement in PROC PRINT or the COLUMNS statement in PROC REPORT is all you need (and much simpler too).