topic Comparing Same Reports of two different dates in SAS Programming

Comparing Two Reports generated through SAS and highlighting the changes

VikrantSawatkar — Tue, 12 Jul 2016 18:16:23 GMT

Hi All,

I have a two reports generated through SAS. Let's say, the first report is of dt. 1st July 2016 and the second report is of today's date or any after 1st July. Both of them are having same columns except some updates.

Now I want to create a report for today's date that will highlight all the new/old/changed entries from the past report. The report should highlight what are the new entries (that should be highlighted as New) as well as if there are any changes in the existing observation from the old report then it should be highlighted as 'Changed'. And if the value is changed then the old value should be displayed in the next column of the report.

E.g. - If the old report is showing that the Emp number 20001 had sold 10 items and the new report is showing that he had sold 15 item then it should be flagged as a 'Changed' and I want to highlight what was the old value for that instance if the value is changed, in the next column (See the sample table below). Same should be displayed for every column from the report.

Table A: (Dt. 1st July 2016)

Emp Number Items Sold Revenue

20001 15 $135

20002 11 $99

20003 10 $95

Tabel A : (Today's Report to be shown like)

Emp Number Items Sold Items Sold Revenue Revenue Flag

(as per old file) (as per old file)

20001 15 15 $135 $135 Old

20002 11 11 $100 $99 Changed

20003 13 10 $95 $95 Changed

20004 12 . $110 . New

My approach is bit lengthy, I guess. What I do is :

1. I Run the Report as per today's input files and rename the variables by adding prefix as 'New' (E.g. New_Items_Sold, etc.)

2. Import the old Report and rename its variables by adding prefix as 'Old' (E.g. Old_Items_Sold, etc.)

3. check their values by using if conditions and if the values are not same then I flag it as 'Changed' and if new Emp Number found then I flagged it as 'New'

4. Then again export the report.

Is there any different approach that will reduce my time? Please share it if any.

Many thanks in advance.

Kind Regards,

Vikrant

Comparing Same Reports of two different dates

VikrantSawatkar — Tue, 12 Jul 2016 18:11:50 GMT

Hi All,

Table A: (Dt. 1st July 2016)

Emp Number Items Sold Revenue

20001 15 $135

20002 11 $99

20003 10 $95

Tabel A : (Today's Report to be shown like)

Emp Number Items Sold Items Sold (as per old file) Revenue Revenue (as per old file) Flag

20001 15 15 $135 $135 Old

20002 11 11 $100 $99 Changed

20003 13 10 $95 $95 Changed

20004 12 . $110 . New

My approach is bit lengthy, I guess. What I do is :

1. I Run the Report as per today's input files and rename the variables by adding prefix as 'New' (E.g. New_Items_Sold, etc.)

2. Import the old Report and rename its variables by adding prefix as 'Old' (E.g. Old_Items_Sold, etc.)

3. check their values by using if conditions and if the values are not same then I flag it as 'Changed' and if new Emp Number found then I flagged it as 'New'

4. Then again export the report.

Is there any different approach that will reduce my time? Please share it if any.

Many thanks in advance.

Kind Regards,

Vikrant

Re: Comparing Two Reports generated through SAS and highlighting the changes

ballardw — Tue, 12 Jul 2016 19:42:22 GMT

As long as the results are not much more complicated than you show I would actually append the to data sets together insuring I have a variable to indicate which set is the source.

The using proc report have the column similar to items_sold,source revenue,source and then a computed column for flag that compares the values of pairs of the columns to yield same or different.

Re: Comparing Same Reports of two different dates

ballardw — Tue, 12 Jul 2016 19:49:04 GMT

Append the two sets together adding a source variable to indicate which is the old and new data.

Use proc report with the variables and the source as sub-column which looks like items_sold,source and similar.

Define a computed column for flag comparing the values of pairs of columns. Basically moving the If/then into the report procedure instead of in data sets.

If you want to do this with many different source files you'll end up spending a certain amount of time dealing with the column aliases in the compute block.

Re: Comparing Same Reports of two different dates

VikrantSawatkar — Wed, 13 Jul 2016 17:47:12 GMT

Thank you very much.

But what if there are more than 10 reports having around 20-25 variables. And I have to implement the above said feature in it.

Re: Comparing Two Reports generated through SAS and highlighting the changes

LinusH — Wed, 13 Jul 2016 18:07:01 GMT

Create a proper data mart data model that let you as flexible as possible create standard reports.

Re: Comparing Two Reports generated through SAS and highlighting the changes

VikrantSawatkar — Mon, 18 Jul 2016 16:57:27 GMT

Thank you very much for your help. I will try to implement it and will let you know once it is successfully implemented.