Hello all SAS guru's
I have a data set that has 4 columns.
PART_NUM (character)
SUB_ASSEMBLY (numeric)
DATE (date)
VALUE (numeric)
What I'm looking to do is get an average of VALUE from the first 2 and an average of VALUE from the last 2 records of the data set based on DATE and SUB_ASSEMBLY for each PART_NUM.
Each part_num can have a different number of sub_assembly values.
For example if the data set was: (data is in columns. doesn't copy very well with just text)
PART_NUM SUB_ASSEMBLY DATE VALUE
A 0 3/1/2010 5
A 1 3/1/2010 4
A 2 3/1/2010 5
A 0 3/2/2010 4
A 1 3/2/2010 4
A 2 3/2/2010 5
B 0 3/1/2010 5
B 1 3/1/2010 4
B 0 3/2/2010 4
B 1 3/2/2010 4
...
A 0 3/20/2010 6
A 1 3/20/2010 7
A 2 3/20/2010 4
A 0 3/21/2010 4
A 1 3/21/2010 5
A 2 3/21/2010 3
B 0 3/20/2010 6
B 1 3/20/2010 7
B 0 3/21/2010 3
B 1 3/21/2010 5
This would yield a table with 4 columns with unique PART_NUM, SUB_ASSEMBLY and two "averaged" columns.
PART_NUM SUB_ASSEMBLY Average(First2) Average(Second2)
A 0 4.5 5.0
A 1 4.0 6.0
A 2 5.0 3.5
B 0 4.5 4.5
B 1 4.0 6.0
As an example, the first row of the output table with PART_NUM "A" and SUB_ASSEMBLY "0"
the average(First 2) is the avg of 5 on 3/1/2010 and 4 on 3/2/2010
the average(Second 2) is the avg of 6 on 3/20/2010 and 3 on 3/20/2010
I am using Enterprise Guide and was hoping that there was a way to do this within that environment. I can't figure one out.
So I'm hoping that some somebody either knows how or can give some advice on SAS code that will solve this.
I'm also hoping I explained this clearly enough.
thanks