Merging 2 datasets, column location a real pain

[CharlesR]

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?

[CharlesR]

Never mind, just switch the order of the datasets in the merge statement

[SPR]

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]

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]

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]

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]

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]

...

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]

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