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

Hi,

I have two datasets in long format that have variables “ID” and “incident_number” in common. Each person has a unique ID number but two or more people can have the same incident_number if they violate school policy together (for example, both of them engaged in underage drinking at the same party; so the underage drinking at the party would count as one incident).

If DatasetB doesn’t have a matching ID and incident number with DatasetA, I want to delete unmatched cases in DatasetB. So, I want to select cases based on a combination of ID and incident number.

Is there a way to modify DatasetB in this way?

Have DatasetA:

ID

Incident_number

VarA

VarB

1

56789

Abcd

1232

1

56789

Erfsdjdgr

94847

2

64908

Fakjsdf;asie

5342

2

56789

Sajfldkjsa;ljf

54698734

3

78621

Kjdafjdh

0589576

4

78621

afsdfasdkljfa

1154

4

78721

Fsafd

987888

Have DatasetB:

ID

Incident_number

VarX

VarY

1

56789

56789

galdjfoia

1

56789

56789

sadjfakhd

1

56818

56818

weoiatlk

2

56789

124363

Abjdkafh

2

64826

5463

Giagaojdfl

3

78629

1545744654

Ajfu

3

78799

1265841

Fkajhdfajh

4

78621

9378464

Nadfaei

4

79725

5486513541

Lirueoi

9

79102

23756

Nann

12

80215

987543

Ueyrf

Want new_DatasetB

ID

Incident

VarX

VarY

1

56789

56789

galdjfoia

1

56789

56789

sadjfakhd

2

56789

124363

Abjdkafh

4

78621

9378464

Nadfaei

Thank you for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Hash Table ?

data DatasetA;
input 
ID
Incident_number
VarA $
VarB ;
cards4;
1
56789
Abcd
1232
1
56789
Erfsdjdgr
94847
2
64908
Fakjsdf;asie
5342
2
56789
Sajfldkjsa;ljf
54698734
3
78621
Kjdafjdh
0589576
4
78621
afsdfasdkljfa
1154
4
78721
Fsafd
987888
;;;;
run;
data DatasetB;
input 
ID
Incident_number
VarX 
VarY $;
cards4;
1
56789
56789
galdjfoia
1
56789
56789
sadjfakhd
1
56818
56818
weoiatlk
2
56789
124363
Abjdkafh
2
64826
5463
Giagaojdfl
3
78629
1545744654
Ajfu
3
78799
1265841
Fkajhdfajh
4
78621
9378464
Nadfaei
4
79725
5486513541
Lirueoi
9
79102
23756
Nann
12
80215
987543
Ueyrf
;;;;
run;

data want;
 if _n_ eq 1 then do;
  declare hash h(dataset:'DatasetA');
  h.definekey('ID','Incident_number');
  h.definedone();
 end;
set DatasetB;
if h.check()=0;
run;

Xia Keshan

View solution in original post

4 REPLIES 4
Miracle
Barite | Level 11

Try this,

proc sort data=a; by ID Incident_number; run;

proc sort data=b; by ID Incident_number; run;

data want; merge a(in=a) b(in=b); by ID Incident_number; if a=b; drop VarA VarB; run;

proc print noobs; run;

Angi
Obsidian | Level 7

Thank you K C Wong. Is there any way of achieving the same result without merging the datasets?

Ksharp
Super User

Hash Table ?

data DatasetA;
input 
ID
Incident_number
VarA $
VarB ;
cards4;
1
56789
Abcd
1232
1
56789
Erfsdjdgr
94847
2
64908
Fakjsdf;asie
5342
2
56789
Sajfldkjsa;ljf
54698734
3
78621
Kjdafjdh
0589576
4
78621
afsdfasdkljfa
1154
4
78721
Fsafd
987888
;;;;
run;
data DatasetB;
input 
ID
Incident_number
VarX 
VarY $;
cards4;
1
56789
56789
galdjfoia
1
56789
56789
sadjfakhd
1
56818
56818
weoiatlk
2
56789
124363
Abjdkafh
2
64826
5463
Giagaojdfl
3
78629
1545744654
Ajfu
3
78799
1265841
Fkajhdfajh
4
78621
9378464
Nadfaei
4
79725
5486513541
Lirueoi
9
79102
23756
Nann
12
80215
987543
Ueyrf
;;;;
run;

data want;
 if _n_ eq 1 then do;
  declare hash h(dataset:'DatasetA');
  h.definekey('ID','Incident_number');
  h.definedone();
 end;
set DatasetB;
if h.check()=0;
run;

Xia Keshan

Angi
Obsidian | Level 7

Thank you very much xia keshan!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 924 views
  • 4 likes
  • 3 in conversation