BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Benn
Calcite | Level 5

Hi Guys,

I currently have 2 sets of data: A and B,

and within each data set, there are 2 variables: Date, Key.

Basically, what I am trying to do is to identify the observations that are FOUND in B and NOT FOUND in A on a certain date with a certain key and then extract them into another set of data.

So for eg:

Set A                                                                   Set B

Date                            Key                                 Date

20080330                      1                                 20080330               1

20080330                      2                                 20080330               2

20080330                      3                                 20080330               4

20080401                      1                                 20080401               1

20080401                      2                                 20080401               2

20080401                      4                                 20080401               3

So in this case, Set C (which is the set I wish to create) will have the following observations:

Date                          Key

20080330                4              

20080401                3

The important thing to note that Dataset C should contain only observations that are FOUND in B but NOT FOUND in A. (Not the other way round or both ways round).

Many thanks guys!

1 ACCEPTED SOLUTION

Accepted Solutions
KarlK
Fluorite | Level 6

Made to order for the little-used "except" set operator:

proc sql;

create table c as

select * from b except select * from a;

quit;

Karl

View solution in original post

4 REPLIES 4
Fraktalnisse
SAS Employee

Hi!

It sounds like a Proc Sql join.

For example:

proc sql;

create table c as

select b.date,b.key

from a right join b

on a.date=b.date and

   a.key=b.key

where a.date=. and a.key=.;

quit;

SteveDenham
Jade | Level 19

After a sort, a merge with in= option should give what you are looking for.

proc sort data=dataset_a;

by date key;

run;

proc sort data=dataset_b;

by date key;

run;

data dataset_c;

merge dataset_a (in=aa) dataset_b(in=bb);

by date key;

if bb and not aa;

run;

There is probably a proc sql approach that is better, but this is one that has worked across many versions of SAS.

Steve Denham

KarlK
Fluorite | Level 6

Made to order for the little-used "except" set operator:

proc sql;

create table c as

select * from b except select * from a;

quit;

Karl

joehinson
Calcite | Level 5

Without doubt, the best solution is Proc SQL with the EXCEPT set operator.

For a DATA step solution without the need for sorting, here is a simple hash solution (using the plural forms of the variable names):

data setc;

     declare hash a(dataset:"seta",ordered:"a");

     a.defineKey("dates","keys");

     a.defineDone();

     do until(done);

          set setb end=done;

          if a.check() ne 0 then output;

     end;

     stop;

run;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 5359 views
  • 7 likes
  • 5 in conversation