topic Remove non-common variables from concatenation in SAS Programming

Remove non-common variables from concatenation

edwolfe — Tue, 16 Nov 2021 17:42:49 GMT

I want to concatenate two datasets (HAVE1 & HAVE2) and obtain a new dataset that only contains the variables that are in common (WANT) without having to explicitly DROP variables from the concatenated dataset. When I combine the two datasets using the SET statement (CONCAT), I get all variables from both of the original datasets (GET).

EXAMPLE CODE:

data have1;
input var1 var2;
datalines;
11 21
12 22
13 23
;
run;

data have2;
input var1 var2 var3;
datalines;
14 24 31
15 25 32
16 26 33
;
run;

data want;
input var1 var2;
datalines;
11 21
12 22
13 23
14 24
15 25
16 26
;
run;

data concat;
set have1 have2;
run;

data get;
input var1 var2 var3;
datalines;
11 21 .
12 22 .
13 23 .
14 24 31
15 25 32
16 26 33
;
run;

Re: Remove non-common variables from concatenation

maguiremq — Tue, 16 Nov 2021 18:04:58 GMT

This does it, but it depends on exactly what you want. If you want duplicates removed, you can omit the `ALL` from the `UNION` operator.

proc sql;
	create table 	want2 as
		select
					*
		from
					have1
						union corr all 
		select
					*
		from
					have2;
quit;

I think that's the difference between the two, but someone can correct me if I'm mistaken.

Re: Remove non-common variables from concatenation

Kurt_Bremser — Tue, 16 Nov 2021 18:55:57 GMT

proc sql noprint;
select t1.name into :keeplist separated by" "
from dictionary.columns t1 inner join dictionary.columns t2
on upcase(t1.name) = upcase(t2.name)
where t1.libname = "LIB1" and t1.memname = "DS1"
and t2.libname = "LIB2" and t2.memname = "DS2";
quit;

data want;
set
  lib1.ds1
  lib2.ds2
;
keep &keeplist.;
run;