How to get wanted dataset

[learn2]

How do I get the wanted dataset by matching on ID?

Dataset 1 

ID	Cases (0=Control, 1= Case)	Injury date	Start of follow up
1	0	Aug21,2009	Aug21,2011
2	1	Jan23,2007	Jan23,2009

Dataset 2

ID	Visits to doctor
1	Sept2,2009
1	Oct30,2011
1	Dec1, 2011
2	Feb7, 2000
2	Feb15, 2010

Data wanted in the following format:

(only interested in visits that are after the follow-up date.  So once I get the desired table below, I basically have to create a count of how many visits a person has had once follow-up starts, for each of the cases and controls

ID	Cases (0=Control, 1= Case)	Injury date	Start of follow up	Visits to doctor
1	0	Aug21,2009	Aug21,2011	Sept2,2009
1	0	Aug21,2009	Aug21,2011	Oct30,2011
1	0	Aug21,2009	Aug21,2011	Dec 1, 2011
2	1	Jan23,2007	Jan23,2009	Feb 7, 2000
2	1	Jan23,2007	Jan23,2009	Feb 6, 2010

[Reeza]

This is known as a merge or join.
https://video.sas.com/detail/video/4572997800001/merging-sas-tables-in-a-data-step?autoStart=true&q=...

[learn2]

I am aware but it is much easier to do a merge when you have observations against one ID a row, but how do I structure the program to output IDs vertically instead of horizontally as shown above. That's what I don't understand - I specifically need help with the code

[Reeza]

---

@learn2 wrote:

I am aware but it is much easier to do a merge when you have observations against one ID a row, but how do I structure the program to output IDs vertically instead of horizontally as shown above. That's what I don't understand - I specifically need help with the code

---

Did you try a basic merge? It's a one to many merge but I don't see any issues with a basic join as shown in @r_behata code. 

[learn2]

Yeah I did but I am getting missing values for the Cases/Controls column even though original cohort has no label missing for the IDs. 

[r_behata]

A sample for sas datastep merge.

data ds1;
input id cases inj $ str $;
cards;
1 0 aug21 aug21
2 1 jan23 jan23
run;


data ds2;
input id vs$;
cards;
1 sept2
1 oct30
1 dec1
2 feb7
2 feb15
run;

data want;
merge ds1 ds2;
by id;
run;

[learn2]

This is actually what I originally did too, but then some observations are missing the Cases/Controls in a row , even though the original cohort did not have missing values...Why could this occur?

[Reeza]

---

@learn2 wrote:

This is actually what I originally did too, but then some observations are missing the Cases/Controls in a row , even though the original cohort did not have missing values...Why could this occur?

---

Show us a situation where this has occurred and then we can tell you why. Given what you've posted no such thing would occur. 

[learn2]

I get results as shown in table below. The two datasets merged properly for ID 2 but didn’t for ID 1

ID	Cases (0=Control, 1= Case)	Injury date	Start of follow up	Visits to doctor
1	.	.	.	Sept2,2009
1	.	.	.	Oct30,2011
1	.	.	.	Dec 1, 2011
2	1	Jan23,2007	Jan23,2009	Feb 7, 2000
2	1	Jan23,2007	Jan23,2009	Feb 6, 2010

[Reeza]

Your IDs are not matching then, even though you think they do. Is there any notes, warnings or errors in the log that tell you anything?

This can happen because you have trailing spaces or invisible characters in one of your data sets or if you have numeric and your underlying variable is actually 1.0001 or something like that. 

Try using COMPRESS() to clean your data first and convert your ID's to character and then try to join your data.

---

@learn2 wrote:

I get results as shown in table below. The two datasets merged properly for ID 2 but didn’t for ID 1

ID	Cases (0=Control, 1= Case)	Injury date	Start of follow up	Visits to doctor
1	.	.	.	Sept2,2009
1	.	.	.	Oct30,2011
1	.	.	.	Dec 1, 2011
2	1	Jan23,2007	Jan23,2009	Feb 7, 2000
2	1	Jan23,2007	Jan23,2009	Feb 6, 2010

---