Desktop productivity for business analysts and programmers

Overlapping dates

New Contributor
Posts: 2

Overlapping dates

I'm currently working on SAS enterprise to create some predicting datasets where the dates overlap but don't want to overwrite the overlapping data for example

Header 1JanAprJulOct

I want to make a new prediction every quarter with the newest data and compare with the prediction made using the previous data. So the effectivedates will overlap but I don't want the data to be overwrittern each time which happens when using effectivedates. I believe I can do this using do loops but am not completely sure how. Any advice on what code to use would be greatly appreciated!!

Trusted Advisor
Posts: 3,208

Re: Overlapping dates

I do not know what kind of analytics and predictions you want to go for.

Is Organizing the data different an option?   The time intervals are different, but you prediction looks the be monthly based.

Changing form qtr runs to monthly runs still be possible, may be to be added as missings. Shifting values in an array should give new diiference columns.   


             val-0   prdct-mnd1  prdct-mnd2  pdrct-mnd3 prdtc mnd4







---->-- ja karman --<-----
New Contributor
Posts: 2

Re: Overlapping dates

I need an individual prediction for each month which is the issue so can't just group them as a quarter. Could I still use arrays if I kept it in the month format do you think? Thanks

Super User
Posts: 5,391

Re: Overlapping dates

Is this data to be store prediction between years as well, or is it a fixed matrix?

To make this flexible transpose the data to 3NF:

month     pred_month pred_seq

Jan     Jan          0

Feb     Jan     1

Mar     Jan     2

Apr     Jan     3

May     Jan     4

Apr     Apr     0

May     Apr     1


Data never sleeps
Community Manager
Posts: 2,889

Re: Overlapping dates


Welcome to the community!  I think that is on the right track for your solution.  By normalizing the data to have one record for each Date/PredictionDate combination, you provide yourself with more flexibility for comparisons between predictions and with actuals, when you have them.  I'll add that it can be an advantage to format your date values as actual SAS dates, so you can use SAS date functions and features like SERIES plots to visualize your results.

Here's an example with your data.  The DATE column is the date-of-prediction (your quarter), PREDMONTH is the month being predicted, and of course PREDICTION is your prediction value.

data predictions;
length Date 8 PredMonth 8 Prediction 8;
format date monyy7. PredMonth monyy7.;
informat date monyy7. PredMonth monyy7.;
input date PredMonth prediction;
Jan2014 Jan2014 0
Jan2014 Feb2014 1
Jan2014 Mar2014 2
Jan2014 Apr2014 3
Jan2014 May2014 4
Apr2014 Apr2014 0
Apr2014 May2014 1
Apr2014 Jun2014 2
Apr2014 Jul2014 3
Jul2014 Jul2014 0
Jul2014 Aug2014 1
Jul2014 Sep2014 2
Jul2014 Oct2014 3
Jul2014 Nov2014 4
Oct2014 Oct2014 0
Oct2014 Nov2014 1
Oct2014 Dec2014 2

/* quick plots to compare predictions */
proc sgpanel =predictions;
panelby date;
series x=PredMonth y=prediction ;

proc sgplot =predictions;
series x=PredMonth y=prediction / group=Date;

The resulting plots for your sample data (just placeholders, I know) aren't too informative, but if you apply your real data you might find these plots useful.  And they are easy to enhance with useful legends, axis behaviors, and more.


Trusted Advisor
Posts: 3,208

Re: Overlapping dates

The idea is you have an individual prediction for each month. The switch is not trying to use absolute references as you did, but as a relative offset to each records identification.

By that every record will get a consistent logical meaning that is far more extentsible to every period. 

---->-- ja karman --<-----
Trusted Advisor
Posts: 3,208

Re: Overlapping dates

I agree on the direction for a 3NF approach Third normal form - Wikipedia, the free encyclopedia it must be also in a 1nf First normal form - Wikipedia, the free encyclopedia that states there must be some atomicity. The atomicity is not having a absolute meaning. It depends on what you are defining as the most elementary way for storing the data as a dwh approach. There are more valid solutions for that.

(see criticism)

Do not expect the data to be ready able to run directly. You often will need to transform the data to be fit on what a ots (off the shelf) procedure is expecting.    

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 6 replies
  • 4 in conversation