Merging two datasets by MATCHED dates

[VDD]

you need to fix get the incoming data so that the variables are the same type.  If a variable is defined in two datasets or incoming process's as both character and numeric they will not stack.  

[alotaibifm]

I fix it. now I need to make sure the date in the (demographic) file match with the (disease) file. 

The date in the (demographic) file looks like this (28MAY2016), 

while the date in the (disease) file looks like this (01/29/2011). 

[Reeza]

They do not need to look the same, but they need to be numeric variables with a DATE format. you can use the FMTINFO function with the CAT option to check what if the format is a date format if you're not sure.

[alotaibifm]

I tried to check the format of the date I have with this code

data library.E11;
a=fmtinfo('best','cat');
put a=;
run;

but I got this error 

ERROR 68-185: The function FMTINFO is unknown, or cannot be accessed.

I'm working on SAS 9.4 TS Level 1M0

[Reeza]

Ah...it's as of 9.4 M3 unfortunately. You can still check the formats manually and see if it's a date, this just helps. Date formats are in the documentation under the Date category. If it's something like Date9, mmddyy or ddmmyy or the like it's a date. At any rate, when merging, the underlying values are used so you want to remove the formats like someone did earlier to illustrate your dates were read in incorrectly.

[alotaibifm]

Reeza, thank you so much for helping me. I really appreciate that so much.

What do you mean by a documentation? I only have excel files without any documentation. I only have access to the excel files.

Is there any way that I can convert all the date to numeric and be done with it?

[alotaibifm]

@Reeza

when I applied this code 

data library.E111;
set library.E1;
Visit_Date= input(DIAG_DT_TM,anydtdte32.);
Format Visit_Date date9.;
drop DIAG_DT_TM;
run;

I got this response in the log 

4113 data library.E111;
4114 set library.E1;
4115 Visit_Date= input(DIAG_DT_TM,anydtdte32.);
4116 Format Visit_Date date9.;
4117 drop DIAG_DT_TM;
4118 run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
4115:19

That's mean (library.E1) has a numeric date. right? 

[Reeza]

It likely means diag_dt_Tm is already a numeric variable. What is the format on the variable? If it's datetime, perhaps you should be applying the DATEPART() function instead of the INPUT().

[alotaibifm]

I'm pretty positive that all the dates I have right now are numerical. So, when I apply the code below to specify the year, it gave me only the year, in the past when it was a character, it gave me missing values. Do you get what I mean? 

Here is the code that I used to only specify the year variable. 

data EALLB;
set EALLA;

year=year(DIAG_DT_TM);
RUN;

^

this is to prove that I have numeric dates NOT character. 

but still, I don't know why when I want to merge and match the (visit date), it only gives me 6 observations, which I checked manually and there are a lot of matched dates between the two files. 

I'm lost. @Reeza

[Reeza]

We've been looking at bits and pieces. Post your code, but first ensure it's ready to be reviewed. Make sure it's commented so we understand what you think is happening - which is often the biggest issue. And format it - lazily if you need to - in EG CTRL+I will format your code.

[alotaibifm]

I uploaded the datasets if you wanna have a look. 

Thanks @Reeza