topic Re: Help with Tools in Statistical Procedures

Help with Tools

dsbihill — Tue, 07 Jul 2015 17:33:45 GMT

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

Re: Help with Tools

Steelers_In_DC — Tue, 07 Jul 2015 17:43:11 GMT

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;

Re: Help with Tools

dsbihill — Tue, 07 Jul 2015 17:52:58 GMT

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

Re: Help with Tools

ballardw — Tue, 07 Jul 2015 18:10:50 GMT

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;

Re: Help with Tools

dsbihill — Tue, 07 Jul 2015 19:30:00 GMT

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.

Re: Help with Tools

ballardw — Tue, 07 Jul 2015 20:25:57 GMT

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.