Report creation for reconciliation - best approach

[Jagadeesh2907]

Hi - I have a dataset which is having the reconciliation results of two datasets dst1 and dst2. 

dst1

app_id name 

100      mark

101      steve

102      John

dst2

app_id  name

100       mark

101       Philip

102       John

result dataset: DST3

dst1_appid dst2_appid recon_appid dst1_name  dst2_name  recon_name

100             100            Match           mark            mark            Match

101             101            Match           steve           Philip            Unmatched

102             102            Match           John            John             Match

I want to get a summary report of DST3. like below: 

Column Name   Overall status  No of Matches No of Mismatches

Recon_appid     Match              3                      0

Recon_name     Unmatch         2                      1

Can i use proc report to create this type of report ?  Also, would i be able to create a report where if i click to expand the overall status i will be able to know the details numbers. how can i approach this problem and what tools i can use from SAS.

[ed_sas_member]

Hi @Jagadeesh2907 

A simple way to look for discrepancies between two datasets is to use a PROC COMPARE:

proc compare base=dst1 comp=dst2 out=toprint transpose listall outcomp outbase;
	id app_id ;
run;
proc print data=toprint noobs;
	id _Type_;
run;

More details on the syntax on : https://www.lexjansen.com/pharmasug/2003/Tutorials/tu056.pdf

The layout is not pretty cool but the content is very informative.

Hope this help!

[ballardw]

You likely want to sort both data sets by a common set of variables, such as your App_id and Name variables. It would likely be worth a step to ensure that character variables have the same case as well. "mark" is not equal to "Mark". If you expect that to be treated as equal then address case prior to any comparison.

[Jagadeesh2907]

HI , this is not a query on the reconciliation method. but to get the metrics from the reconciled dataset. i.e. to get the summary report from the DST3. what will be the best method in SAS to get the summary of matches and mismatches as in dst3.

[Jagadeesh2907]

HI , this is not a query on the reconciliation method. but to get the metrics from the reconciled dataset. i.e. to get the summary report from the DST3. what will be the best method in SAS to get the summary of matches and mismatches as in dst3.