Date compare not working in sas - Input from Excel spreadsheet
Experts,
I am having a problem with an Excel file being read into SAS. When I do a proc contents on the field it is defined as DATE9.
However, my compares are not working against the date. When the date from another file is compared to the date if it is less than it works but equal does not.
This code works fine;
run;
Data JUN24;
Set temp_results;
if year(connecteddate) = 2013;
if month(connecteddate)= 06;
if day(connecteddate) = 24;
run;
This code does not:
Data JUN24;
Set temp_results;
if connecteddate = '24JUN2013'd;
run;
Does anyone know what the problem could be?
My import statement looks like this
PROC IMPORT DATAFILE="C:\Documents and Settings\myfiles\My Documents\File1.xlsx" OUT=File1 DBMS=Excel REPLACE;
RUN;
I am having a problem with an Excel file being read into SAS. When I do a proc contents on the field it is defined as DATE9.
However, my compares are not working against the date. When the date from another file is compared to the date if it is less than it works but equal does not.
This code works fine;
run;
Data JUN24;
Set temp_results;
if year(connecteddate) = 2013;
if month(connecteddate)= 06;
if day(connecteddate) = 24;
run;
This code does not:
Data JUN24;
Set temp_results;
if connecteddate = '24JUN2013'd;
run;
Does anyone know what the problem could be?
My import statement looks like this
PROC IMPORT DATAFILE="C:\Documents and Settings\myfiles\My Documents\File1.xlsx" OUT=File1 DBMS=Excel REPLACE;
RUN;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I couldn't tell what was going on. It appeared to have time ut using datepart did not work so I just concatenated month day and year to get the date.
cdate = mdy(month(connecteddate), day(connecteddate), year(connecteddate));
cdate = mdy(month(connecteddate), day(connecteddate), year(connecteddate));
i have looked at the file and the imported results, and as suspected by both chaau and myself, the imported value is not a pure number - it is a date time value in excel.. when importing, SAS converts the excel date into SAS date, but leaves the Excel time component (reflected as decimals) in place..
e.g. 23 May 2013 in your Excel file has 41417.4927777778 as the number value, and when imported, SAS stores it as 19501.492778.
thus, attempting to do a direct comparison of the imported date with "23May2013"d will not work, and neither will datepart as the decimal component is not recognised as a time in SAS.. to resolve this, yes, you could use the MDY function like you have done.. the other way is use the FLOOR function which will remove the decimals
e.g. 23 May 2013 in your Excel file has 41417.4927777778 as the number value, and when imported, SAS stores it as 19501.492778.
thus, attempting to do a direct comparison of the imported date with "23May2013"d will not work, and neither will datepart as the decimal component is not recognised as a time in SAS.. to resolve this, yes, you could use the MDY function like you have done.. the other way is use the FLOOR function which will remove the decimals
ASKER
Thanks for the additional explanation and introducing the floor function.
ASKER
I have attached a sample of the file coming in. I have tried everything and I cannot get the dates to compare correctly.
Can someone look at the dates: connecteddate, initateddate and see if they see what the problem is? This is the file I am reading into SAS.
Date-issue.xlsx