Creating new data set from 2 seperate datasets (SQL)

[vomer]

Hi all,

Quick question:

I have 2 datasets (ds1 and ds2) and they have some common variables (like ID, site name etc.) and some uncommon variables (flags and markers I have created in each)

Is there any way using proc SQL to create a new dataset - that sources from ds1 and ds2 and computes sums from different variables?

Example:

DS1			DS2
Site	Unit	var	Site	Unit	num
A	P1	0	A	P1	1
B	O1	1	B	O1	1
B	O1	1	B	O1	1
C	I1	0	C	I1	0
C	I1	0	C	I1	1
Final DS
Site	Unit	varsum	numsum
A	P1	0	1
B	O1	2	2
C	I1	0	1

[PGStats · Accepted solution]

Try this :

proc sql;

select * from

(select ID, Unit, sum(var) as sumvar from ds1 group by ID, Unit) natural join

(select ID, Unit, sum(num) as sumnum from ds2 group by ID, Unit);

quit;

PG

[vomer]

thanks! that works wonderfully well.