Desktop productivity for business analysts and programmers

Dates when using EXCEPT comparison

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

Dates when using EXCEPT comparison

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.


Accepted Solutions
Solution
‎02-03-2016 08:58 AM
Trusted Advisor
Posts: 1,114

Re: Dates when using EXCEPT comparison

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

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,114

Re: Dates when using EXCEPT comparison

[ Edited ]

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).

Contributor
Posts: 31

Re: Dates when using EXCEPT comparison

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
Solution
‎02-03-2016 08:58 AM
Trusted Advisor
Posts: 1,114

Re: Dates when using EXCEPT comparison

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 269 views
  • 1 like
  • 3 in conversation