topic Re: Monthly report - comparing and reporting on changes in SAS Programming

Monthly report - comparing and reporting on changes

DarthPathos — Thu, 05 Oct 2017 12:31:33 GMT

Good morning all

I work at a Hospital in Toronto, Ontario and I run a monthly report on counts and various stats. Recently we've noticed that as a result of audits / cleanup / reviews of one sort or another, a case that was closed as "Completed" may be moved to "Cancelled", even though the visit happened 3 months ago.

I've now have been asked to generate a second report of changes so that the Finance department can make the necessary adjustments and add the comments that "X changed by Y amount" into their system. I only need to compare the stats from this month to the previous month, and right now the only way I see doing this is by having a "Stats A" and "Stats B" table and then compare each value in A to its corresponding value in B, and then output that to a "Variance" table.

Does anyone have a more efficient or simpler way to approach this? I have over 350 rows, so by the end of the fiscal year, I'm going to be comparing a rather large amount of data.

Any ideas would be greatly appreciated 🙂

Thanks

Chris

Re: Monthly report - comparing and reporting on changes

ballardw — Thu, 05 Oct 2017 15:09:32 GMT

Can you provide with dummy data an example of what one of your "report" data sets looks like? The data not the report.

I don't see any way to get around your "variance table" but how it is constructed may have some options available.

You comment about "350 rows" makes me think the comparison may run better if data is transposed to have a single row per month. Then first. and last. operators coupled with Lag and/or dif functions may apply.

There are some details that are missing. Your problem description implies that this would be a "by case" comparison. Is that correct? If so what goes into the report for a case that had a report in the last month but no activity in the current month?

What about New cases that would not have a previous month to compare?

Are all of the value to compare across months numeric, character or a combination?

What is to be done with a value that was missing in a previous month but is in the current or vice versa? In some respects missing to a value might not be considered a change (though finance folks may be treating this as change from 0).

Are the numeric comparisons to be a numeric difference or a percent change or something else?

Re: Monthly report - comparing and reporting on changes

LinusH — Thu, 05 Oct 2017 15:09:45 GMT

350 rows sounds like a pen and paper exercise but that was perhaps a typo...
So what is the obstacles in your approach? Comparing data doesn't sound hard (not knowing the details of course).
I would strive for keeping all cases in a full history table to be able to keep track on status changes, and use that as basis for the analysis.

Re: Monthly report - comparing and reporting on changes

DarthPathos — Fri, 06 Oct 2017 00:28:34 GMT

Can you provide with dummy data an example of what one of your "report" data sets looks like? The data not the report.

It's strictly numeric data in the cells. I have 350 cost centres, and each week I have to report volumes of patients seen in each of the clinics / areas that correspond to the cost centre. So Cost Centre is the row, Week Number is the column.

Hadn't thought of that. That would work.

There are some details that are missing. Your problem description implies that this would be a "by case" comparison. Is that correct?

Yes, if we're in week 23, I need to compare the value for the Cardiology Cost Centre 1234 to the same cost centre for last week, and put the variance in a new table.

If so what goes into the report for a case that had a report in the last month but no activity in the current month?

Every week I will have data to compare, as we're constantly running clinics.

...What about New cases that would not have a previous month to compare?

All cost centres are assigned at the fiscal year and it's extremely rare to add one in the middle.

Are all of the value to compare across months numeric, character or a combination?

Numeric.

Missing values are treated as 0. I've already built that into my code, as Finance likes to see 0s versus blanks.

Are the numeric comparisons to be a numeric difference or a percent change or something else?

Numeric - simply This Week - Last Week - Difference.

Re: Monthly report - comparing and reporting on changes

DarthPathos — Fri, 06 Oct 2017 00:32:37 GMT

Hi @LinusH - the 350 rows wasn't, but the Monthly should have been Weekly. As I mentioned in my response to ballardrw, I have 350 Cost Centres x 52 weeks, where the values are simply counts of patients seen. Comparing the data isn't hard (I can do it in Excel rather quickly), I just was hoping for a more automated approach. The history table was what I was looking for, I just wasn't sure how to compare this week's table to last week's and generate the variance output. I don't know if PROC COMPARE would work; initially i was going to use PROC SQL but having to type out all the lines of code would drive me nuts.

Not sure if this clarifies anything. Appreciate your time and have a good evening!

Chris

Re: Monthly report - comparing and reporting on changes

ballardw — Fri, 06 Oct 2017 16:47:17 GMT

How about some actual dummy data for two clinics and 3 weeks and what the result should look like?

I'm not going to go to the work of attempting to dummy up some data that may not actually work for your example.

And if your "week" is actually a variable with a date value the data set structure make work better as:

CostCentre DateValue PatientCount

At which point

data want;
   set have;
   by CostCentre DateValue;
   Patientdif = dif (PatientCount);
   if first.CostCentre then PatientDif=0;
run;

adds the patient difference and either Proc Report or Tabulate would create the rows for CostCentre and Columns for the DateValue and display the difference.

Re: Monthly report - comparing and reporting on changes

DarthPathos — Fri, 06 Oct 2017 18:15:17 GMT

I'll work on some dummy data this weekend and post back here when I have something 🙂 (I was hoping this was going to be an easy conversation, apparently I was wrong LOL).

Thanks and have a great weekend - we have our Thanksgiving on Monday so I get three days off - wahoo!

Chris

Re: Monthly report - comparing and reporting on changes

DarthPathos — Wed, 11 Oct 2017 12:42:26 GMT

Hi all

I've created two dummy data sets and attached the Excel file here. Four tabs: First is the summary for Weeks 1 - 10 and the second is the "raw data" file. Third and fourth are the same but for Weeks 1 - 11. Assume Weeks 1-10 was received in Week 11, and Weeks 1-11 was received in Week 12. Values for each cell are random numbers so the variation will be more extreme than I typically see, but it gives an idea of what I'm looking at.

Thanks for your time and hope you're having a good week!

Chris

Re: Monthly report - comparing and reporting on changes

Patrick — Wed, 11 Oct 2017 13:26:58 GMT

@DarthPathos

Haven't you told us that you're getting the data weekly? If so then why do you have 10 weeks in each of your "raw data" tabs?

Is this really how you get the data? If so then what will make things really hard as there isn't either a date or then at least a consecutive count of weeks (with week1 being the first week of the financial year).

Ideally: Post two data feeds (with dummy data) in the structure how you really get them.

Also: Be aware that quite a few forum members won't/can't download Excel attachments.

And last but not least:

If this data is from your finance department and the report for your finance department and it's only about 350 rows then eventually have them do that all in Excel.

Re: Monthly report - comparing and reporting on changes

DarthPathos — Wed, 01 Nov 2017 14:12:00 GMT

Hi @Patrick - I've gone back to Finance and am working to try and come up with a more reliable reconciliation process. I'm hoping that I won't need to do all this extra work; it's becoming much larger and way more complex than originally planned.

Thanks for your time, and hope you're having a great week 🙂

Chris