BookmarkSubscribeRSS Feed
CharlesR
Calcite | Level 5
Hello,
I'm merging 2 datasets. The final dataset has about 60 columns. To continue with my programming, some of the variables i am merging by, and the variables which get added from one of the datasets, appear amongst the first few columns, but i really want them at the end. I know i can go in and manually move them down the list, but this is a real pain in the neck to do over and over as i work in new code and re-run the code.

Is there a simpler way to move a column in a data set?
8 REPLIES 8
CharlesR
Calcite | Level 5
Never mind, just switch the order of the datasets in the merge statement
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Hello CharlesR,

An easy way to order columns as you like is the RETAIN statement. For example if in the I dataset I have variables A,B,C and would like order them like B, C, A then
[pre]
data I;
retain B C A;
set I;
run;
[/pre]
Sincerely,
SPR
CharlesR
Calcite | Level 5
h'm. i have a LOT of variables. As in around 60. So that may or may not work depending. Is there another way that you know of to put a certain set of variables at the end of your dataset?
SPR
Quartz | Level 8 SPR
Quartz | Level 8
It depends on variable names that you use. If it is something like x1,x2,x3, etc. then variable list in the form of x: can help. If your variables are like arbitrary set of names then variable list will look like a--z where a is the first variable in your dataset and z is the last variable.
ArtC
Rhodochrosite | Level 12
if you cannot use a list abbreviation such as suggested above, you can control the order using manually generated lists of variable names. There are macro language techniques for generating these list that have been discussed in other threads in this forum, but when it comes to typing I have little patience so an alternative to typing and an alternative to the macro approach is copy/paste, if you are using windows.

To type without so much typing:
1) Use a proc contents to write the variables to the LISTING destination, consider using the POSITION option.
2) while holding the ALT key and the LMB drag the mouse down the list of variables.
3) copy and paste the list into a fresh editor window
4) rearrange as desired (I like to group variables by use - this makes life easier later when I want to build a VAR or CLASS statement.
5) paste list into LENGTH or RETAIN statements as appropriate (KEEP and DROP also but they do not determine variable order) Message was edited by: ArtC
art297
Opal | Level 21
While you've already figured out how to get the correct order to your merge problem, a way to reorder a couple of fields to the end of a dataset (without having to do much typing) is make combined use of keep, drop and interleaving datasets. E.g.:

data have;
input a b c d e f g;
cards;
1 2 3 4 5 6 7
1 2 3 4 5 6 7
;
run;

data one;
set have (drop=a b);
run;

data two;
set have (keep=a b);
run;

data want;
set one;
set two;
run;

HTH,
Art
chang_y_chung_hotmail_com
Obsidian | Level 7

...

I'm merging 2 datasets. ...<br />

Is there a simpler way to move a column in a data set?<br />

You can do OUTER UNION with CORRESPONDING in PROC SQL and not worry about manually re-ordering variables.

/* test data */
data one;
   a=11; b=12; c=13; output;
run;

data two;
   d=14; a=11; b=12; c=13; output;
   d=24; c=23; b=22; a=21; output;
run;

proc sql;
   select * from one
   outer union corr
   select * from two;
quit;
/* on lst
   a         b         c         d
   11        12        13         .
   11        12        13        14
   21        22        23        24
*/

Patrick
Opal | Level 21
Hi

You probabely could also use EG's query builder wizard to formulate the join (merge). There you can select a whole bunch of columns and move it up and done as needed using the arrows on the side. The resulting table/view will have the columns in the order you've selected.

If furter data step processing of the resulting table is needed then just select under options/results that a view gets created (instead of a data set) and use this view as input for a consecutive data step.

HTH
Patrick

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1020 views
  • 0 likes
  • 6 in conversation