DATA merging

[serena13lee]

I have 4 data sets that look similar to the following: 

DATA1                    DATA2                DATA3                DATA4 

ID          Var            ID      Var             ID         Var          ID      Var

Cat                        Cat                       Apple                   Cat 

M           5              F          5              Sweet      2          F          15

F            4              Dog                     Sour         1          Dog     

Dog                       Short    3                                          Short     3

Short      4                                                                      Tall       13

Tall         4                                                                      Apple

Apple                                                                             Sweet   12

Sweet    5                                                                      Sour      2

I would like to merge them and keep the order of ID non-alphabetical. I know sorting will lose the order/lose corresponding values and not sorting would lose corresponding values. Is there a way I could merge this to get the following format?

DATA test;

set test.test;

merge DATA1 DATA2 DATA3 DATA4; 

run;

Desired Output: 

DATA                    

ID          Var1        Var 2         Var3         Var4

Cat                                                              

M           5                                              

F            4             5                                  15

Dog                                                               

Short      4             3                                    3               

Tall         4                                                  13                  

Apple                                                                            

Sweet    5                                  2             12

Sour                                          1               2                    

[Kurt_Bremser]

Try this:

data test;
merge
  data1 (rename=(var=var1))
  data2 (rename=(var=var2))
  data3 (rename=(var=var3))
  data4 (rename=(var=var4))
; 
by id;
run;

[ShiroAmada]

tRY THIS

PROC SORT DATA=_1;
  BY ID;
PROC SORT DATA=_2(RENAME=(VAR=VAR2));
  BY ID;
PROC SORT DATA=_3(RENAME=(VAR=VAR3));
  BY ID;
PROC SORT DATA=_4(RENAME=(VAR=VAR4));
  BY ID;

DATA ALL;
  MERGE _1 _2 _3 _4;
  BY ID;
  IF ORDER=. THEN ORDER=99;
RUN;

PROC SORT DATA=ALL OUT=ALL_(DROP=ORDER);
  BY ORDER;
RUN;

Assign a number high enough to make it last.

[serena13lee]

@ShiroAmada, thank you for your reply. The following is my attempt with your code. I would like to keep the ID order C, M, F. Is there anyway I can keep that?

data test;
input Id $1 I1 1.;
datalines;
C3
M1 
F5
;
run;

data test2;
input Id $1 I1 1.;
datalines;
F3
C6
;
run;


PROC SORT DATA=test;
  BY ID;
  run; 
PROC SORT DATA=test2(RENAME=(I1=VAR2));
  BY ID;
run; 

DATA ALL;
  MERGE test test2;
  BY ID;
  IF ORDER=. THEN ORDER=10;
RUN;

PROC SORT DATA=ALL OUT=ALL_(DROP=ORDER);
  BY ORDER;
RUN;

Proc print data = all; 
run; 

[ShiroAmada · Accepted solution]

The code should have been like this..

data test;
input Id $1. I1 1.;
datalines;
C3
M1 
F5
;
run;

data test2;
input Id $1. I1 1.;
datalines;
F3
C6
;
run;


DATA test;
 SET test;
 ORDER=_N_;
RUN;

PROC SORT DATA=test;
  BY ID;
  run; 
PROC SORT DATA=test2(RENAME=(I1=VAR2));
  BY ID;
run; 

DATA ALL;
  MERGE test test2;
  BY ID;
  IF ORDER=. THEN ORDER=10;
RUN;

PROC SORT DATA=ALL OUT=ALL_(DROP=ORDER);
  BY ORDER;
RUN;

Proc print data = all; 
run; 

[Ksharp]

Using Hash Table.

https://communities.sas.com/t5/SAS-Programming/Hash-join-with-multiple-tables/m-p/521069

[Astounding]

The suggestions you have received might be helpful, but first you need to clarify the question.  The VAR4 values that you want as the end result don't match the values that you started with in the fourth data set.  Do they need to be corrected?  Do you need to explain why they are correct?

[serena13lee]

@Astounding, you were right that the var4 vaules are incorrect. I have changed them and edited the question. Thanks.