topic Re: How to compare value-to-value between two datasets in SAS Programming

How to compare value-to-value between two datasets

vnreddy — Wed, 18 Mar 2020 15:01:42 GMT

Hi,

I have two datasets with same column names

I need to validate the values based on primary_account_no which column values are matching and not-matching from Old_data to New_data

for e.g., below table has same values till A4_DOB in both the datasets. whereas, A1-A3 values are not matching in New_data with Old_data.

1. I need to show such difference in a new column with Old_data values.

Primary_Account_No	No_apps	A1_DOB	A2_DOB	A3_DOB	A4_DOB	A1_Exist_customer	A2_Exist_customer	A3_Exist_customer
12345678	3	19350119	19650420	19600507	0	M	M	M
12345679	3	19440223	19710705	19700720	0	M	M	M

could someone help me with this problem. I have more than 150 columns to compare.

Thanks

Vennapusa

Re: How to compare value-to-value between two datasets

supp — Wed, 18 Mar 2020 15:48:14 GMT

Have you looked into Proc Compare?

https://documentation.sas.com/?docsetId=proc&docsetTarget=n0c1y14wyd3u7yn1dmfcpaejllsn.htm&docsetVersion=9.4&locale=en

Re: How to compare value-to-value between two datasets

ballardw — Wed, 18 Mar 2020 16:03:44 GMT

Since you don't show anything as a New or Old data set or the desired result for the comparison it is a bit difficult to determine exactly what you want.

Proc Compare may do some of what you want. You would want to make sure the data sets are both sorted by the same variables, your Primary_account_no for example, then use a BY statement with the same variable.

Re: How to compare value-to-value between two datasets

JerryV — Wed, 18 Mar 2020 16:10:54 GMT

How about the proc compare part of this?

data work.carsOrig work.carsNew; set sashelp.cars;
primary_account_no+1;
run;

data work.carsNew; set work.carsNew; if primary_account_no= 3 then make='xxxxxxx'; run;

proc compare base=work.carsorig
compare=work.carsnew
out=differences outnoequal outall
noprint ;
by primary_account_no;
run;

Re: How to compare value-to-value between two datasets

vnreddy — Thu, 19 Mar 2020 14:25:31 GMT

Hi,

I am expecting results something like below:

Description	Primary_Account_No	No_apps	A1_DOB	A2_DOB	A3_DOB	A4_DOB	A1_Exist_customer	A2_Exist_customer	A3_Exist_customer	A4_Exist_customer
Actual_value	12345678	3	19350119	19650420	19600507	0
Expected_value	12345678	3	19350119	19650420	19600507	0	M	M	M
STATUS	12345678	PASSED	PASSED	PASSED	PASSED	PASSED	FAILED	FAILED	FAILED	PASSED
Actual_value	12345679	3	19440223	19710705	19700720	0
Expected_value	12345679	3	19440223	19710705	19700720	0	M	M	M
STATUS	12345679	PASSED	PASSED	PASSED	PASSED	PASSED	FAILED	FAILED	FAILED	PASSED
Actual_value	12345680	2	19670201	19701019	0	0
Expected_value	12345680	2	19670201	19701019	0	0	A	A
STATUS	12345680	PASSED	PASSED	PASSED	PASSED	PASSED	FAILED	FAILED	PASSED	PASSED

Re: How to compare value-to-value between two datasets

vnreddy — Thu, 19 Mar 2020 15:53:22 GMT

Hi,

Based on below table:

Description	Primary_Account_No	No_apps	A1_DOB	A2_DOB	A3_DOB	A4_DOB	A1_Exist_customer	A2_Exist_customer	A3_Exist_customer
Actual_value	12345678	3	19350119	19650420	19600507	0
Expected_value	12345678	3	19350119	19650420	19600507	0	M	M	M
STATUS	12345678	PASSED	PASSED	PASSED	PASSED	PASSED	FAILED	FAILED	FAILED
Actual_value	12345679	3	19440223	19710705	19700720	0
Expected_value	12345679	3	19440223	19710705	19700720	0	M	M	M
STATUS	12345679	PASSED	PASSED	PASSED	PASSED	PASSED	FAILED	FAILED	FAILED
Actual_value	12345680	2	19670201	19701019	0	0
Expected_value	12345680	2	19670201	19701019	0	0	A	A
STATUS	12345680	PASSED	PASSED	PASSED	PASSED	PASSED	FAILED	FAILED	PASSED

can i output something like this:

Variable	Passed	Failed	Total
Primary_Account_No	73	27	100
No_apps	27	73	100