Help using Base SAS procedures

comparing observations in different dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

comparing observations in different dataset

Hi SAS community,

I have two different datasets: 'base' and 'new'. Many of the observations within the two datasets are the same, but a few are not. I would like to identify which observations from 'new' are not in 'base' so that I can create a new dataset 'want' that includes all in 'base' and then the additions from 'new'.

Thanks!


Accepted Solutions
Solution
‎04-03-2012 12:15 PM
Regular Contributor
Posts: 233

Re: comparing observations in different dataset

Posted in reply to sophia_SAS

Assuming that the column names are same in both the tables.

proc sql;

create table temp as

select * from new

except

select * from base;

quit;

View solution in original post


All Replies
Solution
‎04-03-2012 12:15 PM
Regular Contributor
Posts: 233

Re: comparing observations in different dataset

Posted in reply to sophia_SAS

Assuming that the column names are same in both the tables.

proc sql;

create table temp as

select * from new

except

select * from base;

quit;

Regular Contributor
Posts: 233

Re: comparing observations in different dataset

With an example

data base;
input ID name$;
cards;
1 Test
2 Yes
3 Push
4 In
5 Ye
;
run;

data new;
input ID name$;
cards;
1 Test
2 Yes
4 In
5 Ye
;
run;

proc sql;
select * from base
except all
select * from new;
quit;

Output:

ID name

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

3 Push

Respected Advisor
Posts: 3,156

Re: comparing observations in different dataset

Posted in reply to sophia_SAS

On top of Hima's suggestion, further down the road to your goal, create new data set based on your base plus all of obs not base but in new. You can also try to update your base, but that can't be done in one SQL pass:

data base;

input ID name$;

cards;

1 Test

2 Yes

3 Push

4 In

5 Ye

;

run;

data new;

input ID name$;

cards;

1 Test

2 Yes

4 In

5 Ye

6 sfd

;

proc sql;

create table base_new as

select * from new

union corr

select * from base;

quit;

proc print;run;

Haikuo

Respected Advisor
Posts: 3,156

Re: comparing observations in different dataset

Actually new Hash() method in 9.2 makes this job very much native:

data _null_;

  if _n_=1 then do;

     if 0 then set base;

dcl hash h(dataset:'base', multidata:'y');

h.definekey(all:'y');

h.definedata(all:'y');

h.definedone();

  end;

  set new end=done;

   rc=h.ref();

  if done then rc=h.output(dataset:'want');

  run;

Regards,

Haikuo

Frequent Contributor
Posts: 110

comparing observations in different dataset

Thanks Haikuo for your help. The last code does work for when I'm combining the unique observations from base and new.  What amendments do you suggest I do if I simply want to delete the duplicate observations from the new dataset?  (I.e. the duplicates would be those that already exist in dataset base).

Thanks.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 281 views
  • 6 likes
  • 3 in conversation