topic Re: Combining two files with matching variables in SAS Programming

Combining two files with matching variables

Walternate — Tue, 02 Jul 2019 16:24:55 GMT

Hi,

I have two data files. Each one has an ID variable, var1-var7 (which are the same variables on each file), and then the first file has some additional variables not found on the other file. The ID variable is unique on both files, BUT on File1 can sometimes be missing. Some of the IDs match across files, but there are also IDs that are unique to each. If the ID is missing, var1 - var7 will be missing, but other vars can be populated. If the IDs match across both files, var1-var7 will only be populated on File2.

FILE1

ID var1 var2 var3....var7 other_vars...

1 xyz

2 d e f xyz

. xyz

FILE2

ID var1 var2 var3....var7

1 a b c d

3 a b c d

What I want is to combine the files such that:

1. All records from File1 and File2 are kept (even those with no ID)

2. If a record is in both files, the var1-var7 values come from File2

3. If a record is just in File1 or just in File2, all values should remain intact for all variables.

I considered using UPDATE to update File1 using File2 (as the update process is essentially what I want to do), but the missing ID variables seemed to be problematic when I tried. I also tried using a data step merge and then a PROC SQL join, but in both of those cases, either the File1-only or File2-only values were being overwritten.

Re: Combining two files with matching variables

novinosrin — Tue, 02 Jul 2019 16:47:53 GMT

Will this be close to your need?

proc sort data=file1(where=(not missing(id)) out=file1_sort;
by id;
run;

proc sort data=file2(where=(not missing(id)) out=file2_sort;
by id;
run;

data temp;
set file1_sort(in=a) file2_sort(in=b);
by id;
if last.id;
run;

/*Now you could filter file1 and file2 keeping only the missing ids and append them to the above temp*/

Re: Combining two files with matching variables

anhl1206 — Tue, 02 Jul 2019 17:03:46 GMT

Have you tried adding in an identity variable to your dataset and using that to merge? e.g.:

data one;
set file1;
file1=1;
proc sort data=one;
by ID;
run;

data two;
set file2;
file2=1;
proc sort data=two;
by ID;
run;

data new;
merge one two;
by ID;
if file1 eq 1 or file2 eq 1;
run;

Re: Combining two files with matching variables

tsap — Tue, 02 Jul 2019 17:12:58 GMT

DATA WORK.HAVE_1;
FORMAT 	 ID	1. Var1 $1. 	Var2 $1. 	Var3 $1. 	Var4 $1. 	Var5 $1. 	Var6 $1.	Var7 $1.	OtherVar $3.;
INFORMAT ID	1. Var2 $1. 	Var2 $1. 	Var3 $1. 	Var4 $1. 	Var5 $1. 	Var6 $1.	Var7 $1.	OtherVar $3.;
INPUT    ID		VAR1 		VAR2 		VAR3 		VAR4 		VAR5 		VAR6		VAR7		OtherVar;
INFILE DATALINES DLM='|' DSD;
DATALINES;
1||||||||xyz
2|d||e||||f|xyz
.||||||||xyz
;

DATA WORK.HAVE_2;
FORMAT 	 ID	1. Var1 $1. 	Var2 $1. 	Var3 $1. 	Var4 $1. 	Var5 $1. 	Var6 $1.	Var7 $1.;
INFORMAT ID	1. Var2 $1. 	Var2 $1. 	Var3 $1. 	Var4 $1. 	Var5 $1. 	Var6 $1.	Var7 $1.;
INPUT    ID		VAR1 		VAR2 		VAR3 		VAR4 		VAR5 		VAR6		VAR7;
INFILE DATALINES DLM='|' DSD;
DATALINES;
1|a|b|c| | | |d
3|a|b|c| | | |d
;



PROC SQL;
CREATE TABLE WORK.WANT	AS
	SELECT a.ID, b.VAR1, b.VAR2, b.VAR3, b.VAR4, b.VAR5, b.VAR6, b.VAR7, a.OtherVar

	FROM 		WORK.Have_1 AS a
	INNER JOIN	WORK.Have_2 AS b	ON a.ID=b.ID
UNION
	SELECT a.ID, a.VAR1, a.VAR2, a.VAR3, a.VAR4, a.VAR5, a.VAR6, a.VAR7, a.OtherVar
	FROM 		WORK.Have_1 AS a
	LEFT JOIN	WORK.Have_2 AS b	ON a.ID=b.ID
	WHERE b.ID IS NULL
UNION
	SELECT a.ID, a.VAR1, a.VAR2, a.VAR3, a.VAR4, a.VAR5, a.VAR6, a.VAR7, b.OtherVar

	FROM 		WORK.Have_2 AS a
	LEFT JOIN	WORK.Have_1 AS b	ON a.ID=b.ID
	WHERE b.ID IS NULL;
QUIT;

resulting output aligns with the request in your post:

ID	Var1	Var2	Var3	Var4	Var5	Var6	Var7	OtherVar
.								xyz
1	a	b	c				d	xyz
2	d		e				f	xyz
3	a	b	c				d

Re: Combining two files with matching variables

Walternate — Tue, 02 Jul 2019 17:16:56 GMT

That's perfect, thank you!

Re: Combining two files with matching variables

tsap — Tue, 02 Jul 2019 17:20:37 GMT

You're welcome. Glad I could help.