topic DATA merging in SAS Programming

DATA merging

serena13lee — Thu, 13 Dec 2018 19:49:36 GMT

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

Re: DATA merging

Kurt_Bremser — Thu, 13 Dec 2018 11:25:17 GMT

Try this:

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

Re: DATA merging

ShiroAmada — Thu, 13 Dec 2018 11:44:44 GMT

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.

Re: DATA merging

Ksharp — Thu, 13 Dec 2018 12:47:39 GMT

Using Hash Table.

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

Re: DATA merging

Astounding — Thu, 13 Dec 2018 15:31:55 GMT

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?

Re: DATA merging

serena13lee — Thu, 13 Dec 2018 19:13:57 GMT

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

Re: DATA merging

serena13lee — Thu, 13 Dec 2018 19:20:35 GMT

@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;

Re: DATA merging

ShiroAmada — Fri, 14 Dec 2018 00:49:51 GMT

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;