Desktop productivity for business analysts and programmers

Compare Current to Prior Month - missing data

Reply
Frequent Contributor
Posts: 94

Compare Current to Prior Month - missing data

Hello,

 

I have to compare 2 dataset to get a better/worse performance.  How can I care for projects that are new in the current month and data does not exist for it in prior months?  Or projects that were in the prior month and no longer in the current month (due to cancel or completion)?

 

Currently when I compare these two datasets, if one project is in only one dataset, I get an error in the better/worse calculation.   Something about data does not exists.

 

Thank you in advance for any assistance!

Super User
Posts: 11,101

Re: Compare Current to Prior Month - missing data

I would start by considering what I would do without recourse to software.

 

If I am asked to compare current sales last month to current sales this month for a store that has been closed, what would I report?

Same for a store that opened this month. I would report that there is nothing to compare.

 

In a software sense that would mean to reduce the comparison to those that have data, for example where clauses in data set options, to compare and then a separate summary/note/bullet point or what ever makes sense about why comparisons are not possible for the remaining cases.

Frequent Contributor
Posts: 94

Re: Compare Current to Prior Month - missing data

If we do the method as you suggest, then I don't believe we would not be able to get a total rollup.  There are situations such as a project is cancelled, but the funds are moved to a new project.

 

Costs (Prior Month)

Project A $100

Project B $85

Project C $125

Total Budget $310

 

Costs (Prior Month)

Project A $100

Project C $125

Project D $85

Total Budget $310

 

today we do this in excel spreadsheet (manual process) and I had hope to be able to do this in SAS.

Super User
Posts: 11,101

Re: Compare Current to Prior Month - missing data

Time for the basic bits for questions on this formum: provide some starting data, some rules (at least as you understand them) and what the desired output should look like.

 

This might be possible in proc report where you have a grouping variable to indicate which month the data is from, the values of the project variable would be rows and then do a computation between the columns that makes sense for your comparison, with missing data in one of the columns the result would be missing and hopefully for fairly obvious reasons. Then add a summarize after for totaling columns.

Ask a Question
Discussion stats
  • 3 replies
  • 265 views
  • 0 likes
  • 2 in conversation