topic Re: Creating new data set from 2 seperate datasets (SQL) in SAS Procedures

Creating new data set from 2 seperate datasets (SQL)

vomer — Wed, 20 Mar 2013 14:19:18 GMT

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

Re: Creating new data set from 2 seperate datasets (SQL)

PGStats — Wed, 20 Mar 2013 14:39:59 GMT

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;

Re: Creating new data set from 2 seperate datasets (SQL)

vomer — Wed, 20 Mar 2013 15:48:28 GMT

thanks! that works wonderfully well.