topic Re: Dates when using EXCEPT comparison in SAS Enterprise Guide

Dates when using EXCEPT comparison

carmendee — Fri, 29 Jan 2016 20:01:43 GMT

I am comparing files using the EXCEPT logic. It is returning all row rather than the differences. By process of elimination, it appears to be the date. The output in both files is displayed the same (MM/DD/YYYY).

Input from first file is derived from existing sas tables and formatted NLDATMDT10.

Input from other is from .csv import and is formatted MMDDYY10.

proc sql;
CREATE TABLE Attendance_Compare AS

select * from WORK.ATTNYEST
except
select * from WORK.DAILYATTENDANCE;

Help! I tried several date changes and am getting nowhere.

Re: Dates when using EXCEPT comparison

vandhan — Thu, 28 Jan 2016 15:45:45 GMT

How can I solve the problem? I do not need times, just month, day and year to compare.

Carmen Goss
Integrated Data Systems
Winston-Salem/Forsyth County Schools
Ph: (336) 748-4000 ext 34595

cdgoss@wsfcs.k12.nc.us

All e-mail correspondence to and from this address is subject to the North Carolina Public Records Law, which may result in monitoring and disclosure to third parties, including law enforcement. AN EQUAL OPPORTUNITY/AFFIRMATIVE ACTION EMPLOYER

Re: Dates when using EXCEPT comparison

FreelanceReinh — Fri, 29 Jan 2016 20:39:55 GMT

Hi @carmendee,

As a first guess, I would add the keyword CORRESPONDING:

except corresponding

Edit: Now I realize that NLDATMDT10. is a datetime format (unlike MMDDYY10.)! So, the values must be different, hence "EXCEPT" fails. So, if the time part of the datetimes in the first file is always 00:00:00, you could convert the date values from the second file to datetime values by multiplying them by 86400. Otherwise, you could create a date variable in the first dataset by taking the date part of the existing datetime values (using the DATEPART function).

Re: Dates when using EXCEPT comparison

FreelanceReinh — Fri, 29 Jan 2016 21:01:40 GMT

Here is an example:

data have1;
input id mydtm :datetime.;
format mydtm nldatmdt10.;
cards;
1 12AUG2015:11:30:00
2 25MAR2015:14:30:00
2 26MAR2015:08:30:00
;
data have2;
input id mydate :date.;
format mydate mmddyy10.;
cards;
1 12AUG2015
2 26MAR2015
;

proc sql;
create table diff as
select *, datepart(mydtm) as mydate format=mmddyy10. from have1
except corresponding
select * from have2;
quit;

proc print data=diff;
run;

Result:

id        mydate

 2    03/25/2015