topic Re: Selecting First and Last record(s) from a data set in SAS Procedures

Selecting First and Last record(s) from a data set

GDA — Wed, 03 Mar 2010 01:00:03 GMT

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

Re: Selecting First and Last record(s) from a data set

Cynthia_sas — Wed, 03 Mar 2010 06:01:45 GMT

Hi:
You might consider posting your question in the SAS Enterprise Guide forum. Most folks in this forum use SAS code to accomplish their tasks and if you're looking for a point and click approach, the EG forum would be the place for this question.

cynthia

Re: Selecting First and Last record(s) from a data set

ChrisNZ — Mon, 08 Mar 2010 20:44:01 GMT

I don't see how you could do this in EG without creating many repetitive processes.

Here is how you could do it with a bit of code:
[pre]
data T(index=(a=(PART_NUM SUB_ASSEMBLY)));
input PART_NUM $ SUB_ASSEMBLY $ DATE mmddyy10. VALUE;
cards;
A 0 03/01/2010 5
A 1 03/01/2010 4
A 2 03/01/2010 5
A 0 03/02/2010 4
A 1 03/02/2010 4
A 2 03/02/2010 5
B 0 03/01/2010 5
B 1 03/01/2010 4
B 0 03/02/2010 4
B 1 03/02/2010 4
A 0 03/20/2010 6
A 1 03/20/2010 7
A 2 03/20/2010 4
A 0 03/21/2010 4
A 1 03/21/2010 5
A 2 03/21/2010 3
B 0 03/20/2010 6
B 1 03/20/2010 7
B 0 03/21/2010 3
B 1 03/21/2010 5
run;
proc sort data=T out=LIST nodupkey; * build table of all calculation groups;
by PART_NUM SUB_ASSEMBLY ;
data END;
set LIST; * for each calculation group;
OBS=0; * reset observation counter;
do until (LASTOBS); * start iteration for current group;
set T end=LASTOBS key=A; * read data;
OBS+1; * count observations;
if OBS=2 then AVG1=mean(VALUE,PREV_VALUE); * set avg for first 2 observations;
if LASTOBS then output; * no more observations: save averages;
AVG2=mean(VALUE,PREV_VALUE); * set avg for latest 2 observations;
PREV_VALUE=VALUE; * remember value for next iteration;
end;
if _IORC_=1230015 then _ERROR_=0; * reset error flag ;
keep PART_NUM SUB_ASSEMBLY AVG1 AVG2;
run;