SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

how do I keep previous month's results from changing after each monthly update

Reply
Occasional Contributor
Posts: 6

how do I keep previous month's results from changing after each monthly update

I'm new to SAS so I'm trying to figure things out. I run a report that calculates the percentage of people that are compliant with their training. I run it weekly, so the percentages change each week. At the end of the month I run it the final time, then send it out. the problem is, it might say 98.7% for the end of January, but in March January's percentages might change based on people moving from one department to the other. I'm trying to lock the results at the end of the month so it doesn't recalculate the percentages once the month closes out. People move all year long, so January's data will be inaccurate in September because of people moving. Is there a statement or code I could use, or is it much more complicated than I think? :-)

Super User
Posts: 23,244

Re: how do I keep previous month's results from changing after each monthly update

It depends a bit on your set up and the tools you have available, assuming only BASE SAS here' smy recommendation.

It's a data managerment issue. 

When a report is finalized for a particular month you add it to a permanent data set. 

In your 'new' reports use the stored data and the new results are appended so the report still has the old data. 

You do need to be careful that if you need an update or something goes wrong that the permanent dataset doesn't have duplicates. 

 

This also means you're processing only information for shorter periods of time which may make things faster if that helps.

 

 

Occasional Contributor
Posts: 6

Re: how do I keep previous month's results from changing after each monthly update

That actually sounds simple, but I'm not quite sure how to append the new results to the old data. Is that a manual process, or do I need to set up the query to run and append?

Super User
Posts: 23,244

Re: how do I keep previous month's results from changing after each monthly update

You can set up the query to append at the end of the run, or you can set up a separate process you trigger when you're sure it's done and correct. 

 

If you're using EG there's an APPEND task.

PROC Star
Posts: 1,288

Re: how do I keep previous month's results from changing after each monthly update

Let's start with some basics. Which SAS tool (Enterprise Guide, Display Manager...) are you using, and which task or what code are you using to create your report currently?

Super User
Posts: 5,849

Re: how do I keep previous month's results from changing after each monthly update

Another way is to use a SCD Type 2 dimension for your employees. That mean that the reference table will keep track on when an employee worked where.
This will make reporting more flexible, but perhaps a bit trickier to use for reporting.
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 191 views
  • 0 likes
  • 4 in conversation