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