Hello all,
i have a question about proc compare. i know the eg section isnt the right one but i would like to put it here because
i dont know if there are any differences between z/os /windows/unix in sas.... well it doesnt matter for my question.
if i'm absolutely wrong here please move this entry.
now to my question:
i would like to know how many rows are equal and unequal... well thats not the problem.
the problem is how proc compare visualizes the results.
i used a simple proc compare:
proc compare base=work.old compare=work.new outall out=work.equal;
run;
and i get something like this
Type OBS Timestamp
BASE 1 12/19/2013 06:00:04.010
COMPARE 1 12/19/2013 06:00:04.010
DIF 1 ..............................................
PERCENT 1 ..............................................
i dont like the way proc compare visualizes the results. i think the ".................." for equal datasets are uncomely to handle
and very error-prone. is there a way to get the results shown more comfortably? Any other possibility to visualize that there are differences or no differences?
Something like this:
Type OBS Timestamp
BASE 1 12/19/2013 06:00:04.010
COMPARE 1 12/19/2013 06:00:04.010
DIF 1 none / 0 / equal
PERCENT 1 100%
Evenn if there are differences the visualization is horrible:
BASE 1 12/19/2013 06:00:04.010
COMPARE 1 12/19/2013 06:00:03.947
DIF 1 ..................X.XXX.......................
PERCENT 1 ..................X.XXX.......................
I checked the proc compare options offered by sas: Base SAS(R) 9.2 Procedures Guide
but i cant find any useful....
thanks for the help and kind regards,
s
You could post process the output dataset into a nicer format. Whilst the proc compare output is ok for most cases, it can be beneficial to do at least part of it yourself. Recently for instance I had two RTF files to be compared, there were know issues, so a proc compare then post-processing the output from that was the way to go. You could produce a nice report as well. As far as I know there are really only options on what gets output, no real change to operation.
Also, depending on your id variables, you could do the merge yourself manually and then apply any kind of comparison logic yourself:
proc sql;
create table ERRORS as
select COALESCE(A.ID,B.ID) as ID,
A.DATE as DATE_A,
B.DATE as DATE_B,
case when A.DATE is not null and B.DATE is null then "B is missing date"
when A.DATE <= B.DATE then "B date is not before A date"
....
else "" as ERROR
from BASE_DATASET A
full join COMP_DATASET B
on A.ID=B.ID;
quit;
You could post process the output dataset into a nicer format. Whilst the proc compare output is ok for most cases, it can be beneficial to do at least part of it yourself. Recently for instance I had two RTF files to be compared, there were know issues, so a proc compare then post-processing the output from that was the way to go. You could produce a nice report as well. As far as I know there are really only options on what gets output, no real change to operation.
Also, depending on your id variables, you could do the merge yourself manually and then apply any kind of comparison logic yourself:
proc sql;
create table ERRORS as
select COALESCE(A.ID,B.ID) as ID,
A.DATE as DATE_A,
B.DATE as DATE_B,
case when A.DATE is not null and B.DATE is null then "B is missing date"
when A.DATE <= B.DATE then "B date is not before A date"
....
else "" as ERROR
from BASE_DATASET A
full join COMP_DATASET B
on A.ID=B.ID;
quit;
yeah thanks!
hahah should've got to this solution by myself >.<!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: