Fluorite | Level 6

## Column Orders and Retain (how to amend order of variables towards the end of datasets)

I have a quick question which I hope you can help with.

If I have a dataset with many columns and I just want to re-order a couple of them towards the end of the dataset, is there something simple I can use?  Using retain and re-ordering the 25th and 26th variables (y and z in this case), I would have to do the following code for example:

data work.order;

retain a b c d e f g h i j k l m n o p q r s t u v w x z y;

set x.order;

run;

So, I would have to enter the preceding 24 variables before I can amend the order of the last two at the end.  Anything I'm missing/can be done to avoid entering the preceding 24 variables?

Hope that's clear

I did search and found lots on retain but couldn't find anything that specifically helps with the above.

1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

## Re: Column Orders and Retain (how to amend order of variables towards the end of datasets)

Here's a way to get around that:

data want;

if 5=4 then set have (drop=y z);

retain z y;

set have;

run;

The first SET statement doesn't actually read any data (unless you invent some new type of math where 5=4).  But it orders all variables except Y and Z.

5 REPLIES 5
PROC Star

## Re: Column Orders and Retain (how to amend order of variables towards the end of datasets)

Here's a way to get around that:

data want;

if 5=4 then set have (drop=y z);

retain z y;

set have;

run;

The first SET statement doesn't actually read any data (unless you invent some new type of math where 5=4).  But it orders all variables except Y and Z.

Fluorite | Level 6

## Re: Column Orders and Retain (how to amend order of variables towards the end of datasets)

Thanks for the reply!!  I've never seen that before but it worked fine for my dataset.  It's amazing how much there is to learn in SAS even if you 'think' you are fairly proficient

Fluorite | Level 6

## Re: Column Orders and Retain (how to amend order of variables towards the end of datasets)

@Astounding wrote:

Here's a way to get around that:

data want;

if 5=4 then set have (drop=y z);

retain z y;

set have;

run;

The first SET statement doesn't actually read any data (unless you invent some new type of math where 5=4).  But it orders all variables except Y and Z.

Sorry 'Astounding' one quick question - what does the 'if 5=4 then' actually do?  If you take that away, it still works - i.e., code:

data want;

set have (drop=y z);

retain z y;

set have;

run;

Thanks!

PROC Star

## Re: Column Orders and Retain (how to amend order of variables towards the end of datasets)

Since 5=4 is false, the first SET statement never executes.  While you can remove it, the DATA step takes longer to run that way because the first SET statement now actually reads in data.  I'm not sure if this is clever or not, but you could split up the work between the two SET statements:

data want;

set have (drop=y z);

retain z y;

set have (keep=y z);

run;

Fluorite | Level 6

## Re: Column Orders and Retain (how to amend order of variables towards the end of datasets)

Ah ha - OK!  Thanks for the info once again

Discussion stats
• 5 replies
• 1146 views
• 3 likes
• 2 in conversation