BookmarkSubscribeRSS Feed
DarthPathos
Lapis Lazuli | Level 10

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

Has my article or post helped? Please mark as Solution or Like the article!
9 REPLIES 9
ballardw
Super User

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?

DarthPathos
Lapis Lazuli | Level 10

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.   

 

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.

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. 

 

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).

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.

 

 

Has my article or post helped? Please mark as Solution or Like the article!
LinusH
Tourmaline | Level 20
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.
Data never sleeps
DarthPathos
Lapis Lazuli | Level 10

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

Has my article or post helped? Please mark as Solution or Like the article!
ballardw
Super User

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.

 

DarthPathos
Lapis Lazuli | Level 10

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

Has my article or post helped? Please mark as Solution or Like the article!
DarthPathos
Lapis Lazuli | Level 10

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

Has my article or post helped? Please mark as Solution or Like the article!
Patrick
Opal | Level 21

@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.

DarthPathos
Lapis Lazuli | Level 10

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

Has my article or post helped? Please mark as Solution or Like the article!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2039 views
  • 1 like
  • 4 in conversation