topic Re: Merging two datasets by MATCHED dates in SAS Programming

Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 18:10:12 GMT

Hi everyone,

I am struggling with merging two datasets by date. I have the following datasets:

Dataset DEMO:

PT_ID Visit_Date

Dataset Diss:

PT_ID Diagnosis_date ICD-Code

DATA DEMOA;
MERGE DEMO (in=a)
Diss (in=b);
BY PT_ID;
if a and b;
run;

The resulting dataset looks like this:

PT_ID Visit_Date Diagnosis_date ICD-Code

1 31OCT2016 30OCT2016 599

1 31OCT2016 31OCT2016 599

2 29NOV2012 29NOV2012 466

3 07SEP2014 24JUN2013 486

I have two problems here:

1) The date in both files are not matched SOMETIMES, Is there any way that I can round the date if the (Diagnosis date) one or two days after the (ER Visit)??

2) How can I merge the two files without repeating the observations more than once if the patient has matched date?

Thanks,

Re: Merging two datasets by MATCHED dates

VDD — Thu, 01 Nov 2018 18:38:19 GMT

First you need to change Diagnosis_date or visit_date to the same names and add that date column to your merge.

Dataset DEMO:
PT_ID Visit_Date 
 
Dataset Diss:
PT_ID visit_date /*dummy name to merge on */ ICD-Code 
 
 
DATA DEMOA;
MERGE DEMO (in=a)
Diss (in=b);
BY PT_ID visit_date;
if a and b;
run;

Re: Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 19:02:26 GMT

It didn't work. I have tried that option before, but gave me only 4 observations in the merged dataset, which is impossible that over 40,000 observations, only 4 observations were matched by date. I have doubled checked manually and there are a lot of observations have the same dates between the two files.

Re: Merging two datasets by MATCHED dates

VDD — Thu, 01 Nov 2018 19:29:28 GMT

@alotaibifmPlease post sample data in the form of a SAS dataset with datalines that represent your data in the 2 datasets that you are referencing? You need only represent 7-10 records in each dataset to express your concerns.

Re: Merging two datasets by MATCHED dates

Reeza — Thu, 01 Nov 2018 19:42:20 GMT

Is there any errors in your log? Are the variable types and formats the same?

Re: Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 20:00:07 GMT

Attached a sample of my datasets.

Re: Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 20:03:45 GMT

The codes are correct but merge something I don't want. Thus, no errors on the log.

Re: Merging two datasets by MATCHED dates

Reeza — Thu, 01 Nov 2018 20:06:33 GMT

So when I set the formats attached to the variables, everything works correctly. Otherwise it doesn't.

You can set the formats using:

format _numeric_ 8.;

libname demo 'C:\Users\fareeza.khurshed\Downloads';
options nofmterr;

proc sort data=demo.erDiss; by pt_id visit_date;
proc sort data=demo.erdemo; by pt_id visit_date;
data demo;
set demo.erdiss;
format _numeric_ 8.;
data diss;
set demo.erdiss;
format _numeric_ 8.;
run;

DATA DEMOA;
MERGE demo (in=a)
Diss (in=b);
BY PT_ID visit_date;
if a and b;
run;

Re: Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 20:13:55 GMT

I merged the same code without changing the format and gave me the same results. only 4 observations were matched??!! I am sure 100% that there are more than 4 observations should be matched.

Thanks @Reeza

Re: Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 20:15:33 GMT

I uploaded the datasets if you wanna have a look.

Thanks @Reeza

Re: Merging two datasets by MATCHED dates

Reeza — Thu, 01 Nov 2018 20:16:27 GMT

It's something with your data, but not sure what. Where did you originally get the data from?

Re: Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 20:18:37 GMT

I got them from the Bioinformatics department in our institution. I have received the dataset as an excel format and each file is separate than the other.

Re: Merging two datasets by MATCHED dates

Reeza — Thu, 01 Nov 2018 20:21:03 GMT

Are they actually xlsx files or csv? Did you import them using PROC IMPORT? If so, try converting the files to CSV, re-importing and I suspect this issue will go away.

Re: Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 21:13:42 GMT

The files are CSV format. I don't think the problem in the file itself. I just need to know if it's possible to match dates, if yes, what is the appropriate codes. Thanks,

Re: Merging two datasets by MATCHED dates

Reeza — Thu, 01 Nov 2018 21:21:35 GMT

There's nothing wrong with the code, the problem is with the data.

Re: Merging two datasets by MATCHED dates

alotaibifm — Thu, 01 Nov 2018 21:36:03 GMT

can I change the date format from the original file (excel file)? will that help?

Re: Merging two datasets by MATCHED dates

Reeza — Thu, 01 Nov 2018 21:39:01 GMT

Nope, your best bet is to import ID as character and ensure that dates are imported as a SAS date and then try merging it. Make sure to import your data using a data step (not proc import) and verify your types/values.

Re: Merging two datasets by MATCHED dates

ballardw — Thu, 01 Nov 2018 22:19:53 GMT

@alotaibifm wrote:

Attached a sample of my datasets.

Your data shows signs of coming from Excel with date time values.

When I change the format from DATE9 to best12. I see values like 20602.772222 for visit date.

You need to truncate the values so that the values are integers. The DATE9. only displays the formatted integer portion of the data but comparisons are using the decimal portion as well.

Something like this in data step modifying your data is needed.

Visit_date = floor(Visit_date);

Re: Merging two datasets by MATCHED dates

Reeza — Thu, 01 Nov 2018 22:20:57 GMT

If he imported from CSV files that shouldn't have happened....

Re: Merging two datasets by MATCHED dates

ballardw — Thu, 01 Nov 2018 22:24:46 GMT

@Reeza wrote:
If he imported from CSV files that shouldn't have happened....

I agree that the prior steps are somewhat suspect with such values. Unless he didn't set the entire column to the correct display before doing a SAVE AS.

Or perhaps another step such as taking a MEAN or similar statistic of the date field.