topic Re: Report creation for reconciliation - best approach in SAS Programming

Report creation for reconciliation - best approach

Jagadeesh2907 — Tue, 01 Oct 2019 07:48:02 GMT

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.

Re: Report creation for reconciliation - best approach

ed_sas_member — Tue, 01 Oct 2019 14:46:46 GMT

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!

Re: Report creation for reconciliation - best approach

ballardw — Tue, 01 Oct 2019 17:01:50 GMT

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.

Re: Report creation for reconciliation - best approach

Jagadeesh2907 — Mon, 14 Oct 2019 10:49:55 GMT

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.

Re: Report creation for reconciliation - best approach

Jagadeesh2907 — Mon, 14 Oct 2019 10:50:21 GMT