I am looking for a way in sas to look at component changes over time.
Have:
- data set that contains material_used quantities with date from 2011-2015
data set looks like this:
Unit#, Date, Part#, Qty_used
345 01jan2014 5d3423 5
2034 23feb2015 6t5453 10
to many years of data
- data set that contains Expected 2015 usage(but is total for the year)
dataset looks like
Year, Part#, Expected_Qty_used
2015 5d3423 400
2015 6t5453 700
I would like to take the trend data by month from the first data set and then figure out from previous usages how many i think i should use in the current year based on the previous years.
Is there a tool or method in sas that could do this for me?
If not are there any suggestions on how i go about this?
Thanks
I have a start here but I'm not sure what you are trying to do.
data have;
infile cards dsd;
informat date date9.;
format date date9.;
input Unit$ Date Part$ Qty_used ;
cards;
345,01jan2014,5d3423,5
2034,23feb2015,6t5453,10
;
run;
data have2;
infile cards dsd;
input Year Part$ Expected_Qty_used;
cards;
2015,5d3423,400
2015,6t5453,700
;
run;
proc sql;
create table start as
select a.*,b.year,b.Expected_Qty_used
from have a left join
have2 b on
a.part = b.part;
Sorry I know i am not explaining this well. Let me try again.
From my first data set there is many years and about a million records. I want to figure out from all that previous history by part number and month an average trend across the year. I then want to use that trend to change my second data set from a yearly total into a monthly trend based on past history of that part. I need to do that so I can compare my expected usage to my actual usage and account for the trends of previous history.
I hope this helps
Thank you
I think you need to provide an example of what you expect the final output to look like.
I can't tell if you are trying to run a regression to forecast or to partition an annual total into monthly components.
Do any of your part usages have an actual seasonality such as a use of snow-blower parts which might tend to be used in winter or some such? If so you are likely looking at something from the SAS/ETS module and the project will go much better using those tools.
Likely the first step would be to summarize your part usage to a monthly level which might be something like:
proc summary data=material_used nway;
class partNumber date;
var Qty_Used;
output out=MonthTotals (drop=_type_) sum=;
format date YYMON7. ;
run;
Yes many parts do have actual seasonality. Is there a statistical test that can be used to Validate this?
Output i am looking for would look like this
Part# | Year | Month | ActualQuantity | Expected Quantity | |
5d3423 | 2015 | 1 | 70 | 80 | |
5d3423 | 2015 | 2 | 65 | 85 | |
5d3423 | 2015 | 3 | 45 | 40 | |
5d3423 | 2015 | 4 | 25 | 35 | |
5d3423 | 2015 | 5 | 15 | 30 | |
5d3423 | 2015 | 6 | 15 | 10 | |
5d3423 | 2015 | 7 | 0 | 5 | |
5d3423 | 2015 | 8 | 0 | 5 | |
5d3423 | 2015 | 9 | 0 | 10 | |
5d3423 | 2015 | 10 | 0 | 25 | |
5d3423 | 2015 | 11 | 0 | 30 | |
5d3423 | 2015 | 12 | 0 | 45 | |
400 | Total expected for 2015 based on past trends |
So basically I need to take a 1 year estimated Number and generate the properly weighted values by month based on the past trend data. This i hope will allow me to see if I am out of the box on the estimated numbers early in the year. I am not a statistician but do have a good understanding of sas programing and I am really looking for Ideas on what tools i need and the process to go through to get to this end result.
Thank you so much for all your time.
I think you might want to move this to the Forecasting area as this bit of data looks seasonal to me ant the Time Series procedures, which I don't have access to, as part of SAS/ETS look much more likely to be helpful.
Without ETS, if, and this is a big if, you have enough years of data you might run a regression by part and month with year as the independent variable. It wouldn't be very sensitive though and unless the trends are pretty consistent not very precise.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.