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.
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
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).
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.