DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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

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 Smiley Happy

 

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

 

Thanks in advance all.

 

 


Accepted Solutions
Solution
‎05-25-2017 07:59 AM
Super User
Posts: 5,500

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

Posted in reply to Brickinnit

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.

View solution in original post


All Replies
Solution
‎05-25-2017 07:59 AM
Super User
Posts: 5,500

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

Posted in reply to Brickinnit

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.

Occasional Contributor
Posts: 8

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

Posted in reply to Astounding

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 Smiley Happy

 

Thanks again for your time and what a very helpful community Smiley Happy

Occasional Contributor
Posts: 8

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

Posted in reply to Astounding

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!

Super User
Posts: 5,500

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

Posted in reply to Brickinnit

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;

Occasional Contributor
Posts: 8

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

Posted in reply to Astounding

Ah ha - OK!  Thanks for the info once again

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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