BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
carmendee
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

3 REPLIES 3
FreelanceReinh
Jade | Level 19

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

vandhan
Fluorite | Level 6
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
FreelanceReinh
Jade | Level 19

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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