topic Compare rows and select columns that do not match in SAS Programming

Compare rows and select columns that do not match

mazouz — Tue, 18 Aug 2020 09:01:51 GMT

	col1	col2	col3	col4	col5	col6	col7	col8	col9	col10	FIRST DIFF	SECOND DIFF	THIRD DIFF
obs1	-16,13	-6,45	-9,68	ZE	AM	16822	2	2	XXX	09-janv-19	col6	-	-
obs2	-16,13	-6,45	-9,68	ZE	AM	16822	2	2	XXX	16-janv-19	col6	-	-
obs3	-16,13	-6,45	-9,68	ZE	AM	16822	2	2	XXX	23-janv-19	col6	-	-
obs4	-16,13	-6,45	-9,68	ZE	AM	16822	2	2	XXX	30-janv-19	col6	-	-
obs5	16,13	6,45	9,68	ZE	AM	16892	2	2	XXX	09-janv-19	col6	-	-
obs6	16,13	6,45	9,68	ZE	AM	16892	2	2	XXX	16-janv-19	col6	-	-
obs7	16,13	6,45	9,68	ZE	AM	16892	2	2	XXX	23-janv-19	col6	-	-
obs8	16,13	6,45	9,68	ZE	AM	16892	2	2	XXX	30-janv-19	col6	-	-

I want to compare similar rows in (col4 to col10) and their negative matches in col1 col2 col3 and I want to detect if some rows are not similar on col4 to col10; in this example col6 is not similar

Re: COMPARE ROWS AND SELECT COLUMNS THAT DO NOT MATCH

PeterClemmensen — Tue, 18 Aug 2020 08:24:30 GMT

Why col6? i don't understand this logic. Please be more specific.

Re: COMPARE ROWS AND SELECT COLUMNS THAT DO NOT MATCH

andreas_lds — Tue, 18 Aug 2020 08:28:15 GMT

Please don't use only upcase-letters in the subject, this is called "screaming" i prefer not be yelled at.

You will also want to post the data in usable form: a data-step using datalines/cards and extend the data so that all cases you are looking have an example.

Re: COMPARE ROWS AND SELECT COLUMNS THAT DO NOT MATCH

mazouz — Tue, 18 Aug 2020 08:46:18 GMT

data have;
input col1 col2 col3 $ col4 $ col5 $ col6 $ col7 $ col8 $ col9 $ col10 $;
datalines;
-16 -6 -9 ze am o 2 km JH date1
-16 -6 -9 ze am o 2 km JH date2
-16 -6 -9 ze am o 2 km JH date3
-16 -6 -9 ze am o 2 km JH date4
16 6 9 ze am n 2 km JH date1
16 6 9 ze am n 2 km JH date2
16 6 9 ze am n 2 km JH date3
16 6 9 ze am n 2 km JH date4
;
run;

columns from 4 to 10 are class variables they must be similars

Re: COMPARE ROWS AND SELECT COLUMNS THAT DO NOT MATCH

mazouz — Tue, 18 Aug 2020 08:51:09 GMT

because 16822 not similar to 16892
between two rows col4 to col10 must being similars when the columns (col1 col2 col 3) have their negative correspondents

Re: Compare rows and select columns that do not match

RichardDeVen — Tue, 18 Aug 2020 11:18:22 GMT

What are your rules for similarity ?
For the case of computing a columns dissimilar classification, would it be 'the number of distinct character values, or absolute numeric values in the column is greater than 1` ?
Why is column 10 not a DIFF result ?
Are you looking at value run patterns instead of similarities (or equivalences) between two halves of the rows ? If so, what if you have an odd number of rows ?
And, as devils advocate, if only one column has a single value, and all the other columns have varying values, could it be the static column is now the dissimilar one ?

Re: Compare rows and select columns that do not match

mazouz — Tue, 18 Aug 2020 11:54:33 GMT

to put you in context, it's about invoice cancellation and reimbursement. when an invoice is refunded I must have two lines which have the same values for the class variables but the amounts are opposite (-16 against 16).
my objective is to see if two lines are similar for all the variables of class except one or two or three variables which are not similar and to detect these variables not similar to correct them
column 10 not a DIFF result because I have their positive similar