topic Re: Column Orders and Retain (how to amend order of variables towards the end of datasets) in SAS Programming

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

Brickinnit — Thu, 25 May 2017 10:56:05 GMT

Hey Follow SAS Users!

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.

Thanks in advance all.

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

Astounding — Thu, 25 May 2017 11:29:27 GMT

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.

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

Brickinnit — Thu, 25 May 2017 11:59:31 GMT

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

Thanks again for your time and what a very helpful community

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

Brickinnit — Thu, 25 May 2017 13:06:24 GMT

@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!

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

Astounding — Thu, 25 May 2017 13:14:58 GMT

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;

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

Brickinnit — Thu, 25 May 2017 13:21:19 GMT

Ah ha - OK! Thanks for the info once again