topic Compare every columns value from different snapshot in SAS Procedures

Compare every columns value from different snapshot

Shigoto — Fri, 27 Dec 2019 05:32:33 GMT

Could sas be used to compare multiple snapshots of the same tables taken on different days (like each month end)?
There are tables which staff access to enter new data but they could also update value of existing columns. I was ask to check same table snapshot taken in the end of month to extract rows which have some values modified since the first snapshot. And report back the first entered value and the last updated value. Tables contains 30 to 50 columns and each table have a bit less than 600k rows.
I do not think it’s simple and easy to write sql query. Would sas be good at doing such comparisons ?

Re: Compare every columns value from different snapshot

Shmuel — Fri, 27 Dec 2019 08:06:33 GMT

Use proc compare, to compare the 1st snapshot (base😃 with the last one (compare=).

To compare numeric value use option criterion= (like criterion=0.01 for rounded cents).

Use by statement to force comparing same IDs.

Check documentation for more options and possibilities.

Re: Compare every columns value from different snapshot

Shigoto — Fri, 27 Dec 2019 08:18:49 GMT

Thank you for the reply. Most of the values are not number, they are varchar and text. Such as comment and adress. Could ‘by’ be used to compare text value change ? And each table has 30 to 50 columns to be checked if there are difference between 1 month old snapshot, 2 months old one, 3 months old one. So I can only compare two copies at a time with ‘by’ function, is it true?I wonder if It is something rather simple?

Re: Compare every columns value from different snapshot

Shmuel — Fri, 27 Dec 2019 08:59:57 GMT

You can use keep= option in both tables, base and compare, in order to compare selected variables only.

Proc compare will compare all matching selected variables by names, character type as numeric.

You probably don't want to compare address of ID1 with that of ID2. Using by statement will

compare matching IDs. The output list will contain all not matching IDs and all

matching IDS if there are unequal values of matching variables.

Best try it and check output listing.