Merge problem

[deleted_user]

Hi All,

I want to create a dataset with all rows from 'one' except rows from 'two'.i.e i don't want rows from 'two'.problem is that by variable has duplicates.

I usually do like this,

'one' and 'two' are sorted by 'connect' .

data new;
merge one(in=a) two(in=b);
by connect;
if a=1 and b=0;
run;

I am thinking that it will be done by using except operator in proc sql, Can any body give some idea on this?

Thanks.

[deleted_user]

You could try:

proc sql;
create table new as
select * from one where
connect not in (select distinct connect from two);
quit;

This generates a sub query to find all the values of connect in two. The where clause will take only the records from one which do not have a value of connect that is in the list resulting from the sub query.

Hope that's what you are after.