Comparing Two Reports generated through SAS and highlighting the changes

[VikrantSawatkar]

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

[VikrantSawatkar]

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 (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

[ballardw]

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.

[VikrantSawatkar]

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.

[ballardw]

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.

[LinusH]

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

[VikrantSawatkar]

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