DATA Step, Macro, Functions and more

Pulling required observations from 2 data sets

Reply
Occasional Contributor
Posts: 14

Pulling required observations from 2 data sets

Hi,

 

Kindly help me, I want to pull required data from 2 dataset with a data step.

 

eg:

datasetA:

101 bike

102 car 

103 bike

104 car

 

datasetB:

105 car

101 bike 

102 car

 

I want to exclude the observations which are not in datasetB and in datasetA as follow

 

datasetC:

 

101 bike

102 car

103 bike

105 car

 

Note: here In C observation: 104 should be missed , and 105 should be there

 

Thanks in Advance

Santosh

 

PROC Star
Posts: 7,365

Re: Pulling required observations from 2 data sets

Why is 103 bike in the desired set. Are you matching on one or both variables?

Occasional Contributor
Posts: 14

Re: Pulling required observations from 2 data sets

No, I want to keep the records of A and B and exclude the record which is not in B and in A

Frequent Contributor
Posts: 75

Re: Pulling required observations from 2 data sets

[ Edited ]

103 bike is one of two records not in B and in A, so why have you put it in your desired output dataset C?

Super User
Posts: 5,260

Re: Pulling required observations from 2 data sets

Perhaps it's because of long working week, but I don't get the logic. Perhaps if you describe the business requirement it would be a bit clearer.
Also, why data step if e.g. SQL is better suited?
Data never sleeps
Occasional Contributor
Posts: 14

Re: Pulling required observations from 2 data sets

Am working with Clinical data, for Subject Elements, I need to set them One by One, if they are not in datasetB, they are said to be Non-completers of the study, so I've to exclude those subjects from A

 

thanks

Frequent Contributor
Posts: 75

Re: Pulling required observations from 2 data sets

[ Edited ]

This will give you everyone from A who is also in B (i.e. excludes those from A who are not in B)

 

create table C as
select *
from A
where yourIDVar in (select yourIDVar from B);
Occasional Contributor
Posts: 14

Re: Pulling required observations from 2 data sets

thanks for, but it worked for records who are in both the datasets

 

thanks

Santosh

Frequent Contributor
Posts: 75

Re: Pulling required observations from 2 data sets

Let's break this down Santosh. You do not want those obs from A which are not in B, This throws out:

 

103 bike

104 car

 

You say you do want 105 car in dataset C:

 

In this case, are you simply not looking for the dataset B itself? What are we not getting?

Occasional Contributor
Posts: 14

Re: Pulling required observations from 2 data sets

Yes!

Frequent Contributor
Posts: 75

Re: Pulling required observations from 2 data sets

Okay, so are you all set? You just need dataset B? There is no coding problem to solve?

Occasional Contributor
Posts: 14

Re: Pulling required observations from 2 data sets

Not like that, As I said have to exclude the records which are "not in B" and "in A", Have to keep the records which are "in B" and "not in A" 

 

PROC Star
Posts: 7,365

Re: Pulling required observations from 2 data sets

Obviouslly, those of us reading this trhead don't understand what you are trying to do.

 

For each of the 4 records in data set c, separately, please explain why they were selected.

 

Occasional Contributor
Posts: 14

Re: Pulling required observations from 2 data sets

thanks all for your support!

I apologise for not being clear, My situation is like that!

 

Working with clinical data.

 

Let us take an example of Acadamic class,

class6 and class8

Class8 may have promoted students who are succeded, and class6 are striving for success, and if students not in class8 of class6 are said to be failures, and if there are individual students of both the classes are said to be succeeded.

 

still am not clear

No problem

 

thanks again

 

 

 

Contributor
Posts: 43

Re: Pulling required observations from 2 data sets

Your description is called as "left join".

proc sql;
create table dataset_c as
select dataset_a.*, dataset_b.*
from dataset_a LEFT OUTER
join dataset_b on dataset_a.student=table_b.student; quit;
Ask a Question
Discussion stats
  • 14 replies
  • 188 views
  • 1 like
  • 5 in conversation