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
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;
Try this:
data test;
merge
data1 (rename=(var=var1))
data2 (rename=(var=var2))
data3 (rename=(var=var3))
data4 (rename=(var=var4))
;
by id;
run;
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.
@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;
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;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.