BookmarkSubscribeRSS Feed
AliceRose
Calcite | Level 5

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
Jan0
Feb1
Mar2
Apr30
May41
Jun2
Jul30
Aug41
Sept2
Oct30
Nov41
Dec2

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!!

6 REPLIES 6
jakarman
Barite | Level 11

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

qtr-01

qtr-02

qtr-03

qtr-04

qtr-05

qtr-06

---->-- ja karman --<-----
AliceRose
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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

etc

Data never sleeps
ChrisHemedinger
Community Manager

AliceRose,

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;
datalines;
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
;
run;

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

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

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.

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
jakarman
Barite | Level 11

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 --<-----
jakarman
Barite | Level 11

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 1219 views
  • 0 likes
  • 4 in conversation