topic Re: How to get wanted dataset in New SAS User

How to get wanted dataset

learn2 — Wed, 19 Dec 2018 18:06:06 GMT

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

Re: How to get wanted dataset

Reeza — Wed, 19 Dec 2018 18:10:06 GMT

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=merge

Re: How to get wanted dataset

learn2 — Wed, 19 Dec 2018 18:14:12 GMT

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

Re: How to get wanted dataset

r_behata — Wed, 19 Dec 2018 18:20:00 GMT

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;

Re: How to get wanted dataset

Reeza — Wed, 19 Dec 2018 18:27:17 GMT

@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.

Re: How to get wanted dataset

learn2 — Wed, 19 Dec 2018 18:27:36 GMT

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?

Re: How to get wanted dataset

Reeza — Wed, 19 Dec 2018 18:28:44 GMT

@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.

Re: How to get wanted dataset

learn2 — Wed, 19 Dec 2018 18:29:40 GMT

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.

Re: How to get wanted dataset

learn2 — Wed, 19 Dec 2018 21:02:15 GMT

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

Re: How to get wanted dataset

Reeza — Wed, 19 Dec 2018 22:32:57 GMT

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