Merging two datasets by MATCHED dates

[alotaibifm]

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, 

[Reeza]

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

[alotaibifm]

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

[Reeza]

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.

[VDD]

Do the changing in SAS so that you don't have to manually do it in Excel again.

Code once run many.

[Reeza]

And you can get rid of decimals with the round() function. Note that although the name is the same as Excel it operates a little differently so read the docs.

[alotaibifm]

I have 5 files for the (ICD_Codes), so I need to truncate them as one file. when I do that, the dates get messed up. I don't know why this is happening. see the attached picture (last column). 

then I did this code 

data DSNEW;
set dss5;
D_DATE= input(DIAG_DT_TM,anydtdte32.);
Format D_DATE mmddyy10.;
drop DIAG_DT_TM;
run;

to fix it, and maybe I messed it up. the code was only applied for one dataset and I changed the rest by changing the excel date format. 

[Reeza]

I stand by my previous response, fix the data import to avoid all of this. Feel free to post that code and we can help fix it if needed.

Since that variable has dt_tm I would have assumed it was a date time, not a date value. If that’s the case, try anydtdtm instead of anydtdte.

[alotaibifm]

I just want to keep only the date. I'm not interested in the time. Can I keep only the date? 

[Reeza]

---

@alotaibifm wrote:

I just want to keep only the date. I'm not interested in the time. Can I keep only the date? 

---

Sure, use DATEPART() after on the variable to get just the date portion. 

or TIMEPART() for the time portion.

[alotaibifm]

data New_All;
set EALLD_NEW;
D_DATE= input(DIAG_DT_TM,anydtdte32.);
Format D_DATE DATE9.;
drop DIAG_DT_TM;
run;

I did it all over again, and I'm afraid that I will mess up the whole data. I reimported the CSV files through (file>import> etc) then I concatenate all the 5 files in one file. Now, I want to change the date format, so that I can merge them correctly. When I applied the code I posted here, the date shift to 5 years or so, it's not giving me the correct conversion. Do you think it's a correct code? 

[Reeza]

I reimported the CSV files through (file>import> etc)

You really need to import the files with a data step, not an automated import process. Otherwise you'll likely still run into the same issues.

[alotaibifm]

Honestly, I have never imported a data through (data step). I will try to read and find resources to explain the process in detail. If you have any resources, I would appreciate your help with that too.

Do I need to import the (demographic file), which has date as well through data step as well??

[Reeza]

Run proc import. Get the code from the log.
Paste it in, and go through and verify that each field is being read correctly.When doing the PROC IMPORT use GUESSINGROWS=MAX to ensure the best results, but you will still need to manually verify the data. If you have any documentation on the data, or a record layout file you should compare it against that to ensure it's read properly.

Ultimately, remember, garbage in= garbage out.

https://stats.idre.ucla.edu/sas/faq/how-do-i-read-in-a-delimited-ascii-file-in-sas/

[alotaibifm]

I imported the 5 files by (proc import) here is the code for one file: 

proc import
out=library.E1
datafile='C:\Users\alotaibifm\Desktop\Study 3/export1.csv'
dbms=csv
replace;
GUESSINGROWS=MAX;
run;

when I tried to concatenate all 5 files in one file, SAS gave me this error:

4027 data library.Dall;
4028 set library.E1 library.E2 library.E3 library.E4 library.E5;
ERROR: Variable DIAG_DT_TM has been defined as both character and numeric.
ERROR: Variable DIAG_DT_TM has been defined as both character and numeric.
4029 run;

then I tried to convert the date by this code and gave me a lot of missing value. 

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