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
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
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.
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.
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.
Thank you very much for your help. I will try to implement it and will let you know once it is successfully implemented.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.