move variable to specific position

[Smitha9]

Hi,

I have a dataset with 10 variables:

a  b  c  d   e   f   g   h   i   j  

I want to change the position of variable " j" to second position and  "d" to 10th position.

a   j   b   c   e  f   g   h   i   d

I there any other way other than using retain? A short way.

[PaigeMiller]

Yes, the short way is to not move it in the data set. Leave it where it is. Later when outputting something (a report or table) then you can specify the exact location of each column, PROC PRINT and PROC TABULATE and PROC REPORT (and other procedures) make this simple.

[mkeintz]

I think I'm in a minority on this, but I do occasionally reorder variables, even though it's not needed for analysis or reports (but even in these situations it can be convenient to have the variables stored in a particular order).

I do it primarialy because I often view the data using the display component of SAS, or the SAS universal viewer.

Here's how I would suggest 

data want;
  if 0 then set have (keep=a) have (keep=j) have;
  set have;
run;

The "if 0 then set" statement at the beginning obligates the SAS compiler to order the program data vector with a as the leftmost, j as the second, and then all other variables in their original order.  This happens even though actual data input happens only with the second SET statement.

[PaigeMiller]

Well, that's not a minority, there's 1 in favor and 1 opposed. But ... why not create a view with SQL or a view with a data step that re-orders the columns (and/or removes columns) if it helps when looking at the data set?

[FreelanceReinh]

Now we're even a majority :-).

I'd suggest a minor change to the first SET statement to address the requirement regarding variable d:

if 0 then set have(keep=a j) have(drop=d);

[PaigeMiller]

My real problem with the request to re-arrange columns is the same as my problem with many requests where someone has decided on a solution, and doesn't really want to tell us why they have decided on this solution; they haven't even told us what the overall problem is. It is the XY problem, it may be the wrong solution, an inefficient solution or it may be the right solution, we don't know because the person never tells us the reason they want to do this. And based on past experience, even when we ask directly to understand the problem, which I see that I did not do in this case, the user is often reluctant to tell us. This is inefficient for the user, and inefficient for those of us trying to help.

And thus (for example), when the user sees columns in this order APR AUG DEC FEB ... and they really want the columns in the order JAN FEB MAR APR ..., they decide to ask the question about how to rearrange columns, and a better method of getting these columns in the right order in the first place never gets discussed.

So, @Smitha9 , why do you need to re-arrange columns? Explain why you need columns in a specific order, please.

[Smitha9]

Hi,
The order is automated for the next team who goes through the data. I have been asked to edit a variable and add a variable. the output after the edit/add messed the order of the variables. There are like 30variables and that has a specific order for the next team to process. So I want to shortcut way to do this instead writing retain and name all the 30variables in order.
thanks

[PaigeMiller]

So the next team has used code such as

temperature -- pressure

and if you change the order of variables things get all screwed up?

[Reeza]

So you need to specify the order then somehow, manually unfortunately. If you have the order in a list or Excel file then it could be automated but since it's a one time process may as well just hardcode it. 

My lazy way:

proc sql FEEDBACK;
select * 
from sashelp.class as t1;
quit;

Check the log and copy the code, reordering the columns needed manually once - making sure comma's are kept consistent. Done.

proc sql;
create table want_ordered as
         select T1.Name, T1.Sex, T1.Age, T1.Weight, T1.Height
           from SASHELP.CLASS T1;
quit;

---

@Smitha9 wrote:
Hi,
The order is automated for the next team who goes through the data. I have been asked to edit a variable and add a variable. the output after the edit/add messed the order of the variables. There are like 30variables and that has a specific order for the next team to process. So I want to shortcut way to do this instead writing retain and name all the 30variables in order.
thanks

---

[mkeintz]

@Smitha9 said

... "the output after the edit/add messed the order of the variables" ...

Are you saying that variables were in the desired order prior to the edit/add step?  If so, show that code, which likely can be modified to generate variables in the desired order.

[PaigeMiller]

---

@Smitha9 wrote:
Hi,
The order is automated for the next team who goes through the data. I have been asked to edit a variable and add a variable. the output after the edit/add messed the order of the variables. There are like 30variables and that has a specific order for the next team to process. So I want to shortcut way to do this instead writing retain and name all the 30variables in order.
thanks

---

I get the very strong impression that the entire system needs to be re-designed here, so that this re-arranging of variables in a data set is not needed, but since we don't know all the requirements, its impossible to advise. Re-designing the entire system would of course take time, but would result in more efficient code.

Its a tradeoff, re-ordering the variables may be a quick fix; re-designing the system has long-term benefits but doesn't help in the short run. (Yes, I understand this, and have been in this situation on many occasions, and often choose the short term quick fix).

And its still not clear to me that re-ordering the variable is the long-term solution; you could code the re-ordering of variables today, only to find out that the each time thereafter more re-ordering of variables will be needed.

[mkeintz]

---

@FreelanceReinh wrote:

Now we're even a majority :-).

I'd suggest a minor change to the first SET statement to address the requirement regarding variable d:

if 0 then set have(keep=a j) have(drop=d);

---

Sure, as long as J follows A in the original data set.

[noetsi]

This is really easy in proc sql if I understand what you want

proc sql;
Create table work.example
as Select var1, var2,....
from work.datasetname;
quit;

then put it in the select list in the order you want. The name of the dataset and where its stored will vary of course. If its not in a permanent library already you would have to import the data.