Help with Tools

[dsbihill]

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

[Steelers_In_DC]

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;

[dsbihill]

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

[ballardw]

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;

[dsbihill]

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.

[ballardw]

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.