08-29-2017 09:24 PM
08-29-2017 09:37 PM
One explanation for what you are seeing is that the variable you are comparing is in fact a DATETIME but you are only seeing the DATE part of it. What SAS format is assigned to this variable?
08-29-2017 10:37 PM
08-30-2017 03:12 AM
Use PROC CONTENTS to check for assigned formats. PROC COMPARE is indicating there is an assigned format by displaying data in the form DDMONYYYY:
proc contents data = DatasetName; run;
08-30-2017 11:32 AM
Please show the code you are attempting. Compare has a number of options on what to compare and how to display the results.
Also if you are interested in comparisons of observations you may need to sort the data sets by a number of variables to get them into the same order.
08-30-2017 11:55 AM
08-30-2017 12:17 PM
You probably converted one of the files from Excel. In Excel datetime values are stored using the interger part for the date and the fraction as the percent of 24 hours. In SAS the DATE format will ignore the fractional part of the value and just display the date.
404 data _null_; 405 dt1='17Sep2014'd ; 406 dt2=dt1 + 0.6250; 407 put (dt1 dt2) (=) / (dt1 dt2) (date9. +1) ; 408 run; dt1=19983 dt2=19983.625 17SEP2014 17SEP2014
08-30-2017 12:35 PM - edited 08-30-2017 12:40 PM
You will need to decide what to do based on what you need.
If you are just looking to see if the values have the date part and the very few that don't are just because of these fractional parts then you could perhaps just declare success and move on.
If you did want to convert you would use a data step to make a new version of the data with the date values converted. If you have more than one date variable per dataset then you can use ARRAY processing to simplify the code.
For example to just remove the fractional date part using the INT() or ROUND(,1) function.
mydate = int(mydate);
If you wanted to convert the variable to DATETIME instead to preserve the time part then you could use the DHMS() format. It will automatically conver the fractional part for you.
mydate = dhms(mydate,0,0,0); format mydate datetime20.;
08-30-2017 02:05 PM
The import wizard noticed that most of the cells in that column have a date format attached, so it decided to import the value as a date. Since Excel stores dates as number and so does SAS it just copied over the number and adjusted the value to match the difference in base dates that the two systems use. If the value had a fractional date then that was copied along with it. In both SAS and Excel if you format the value with a format that ignores the fractional value then you do not normally see the fraction value, but it is still there in the data.