retain

[Kana]

hi there

I selected the following 20 obs from a data set of 200 000 obs.

BODYNO	DEFECT	ADJKM	NAME OF FORMER	LABEL OF	COL1
			VARIABLE	FORMER VARIABLE
0NP07185	99600668MP	15332	DREP	DREP	20130116
0NP07185	99600668MP	29407	DREP	DREP	20131029
0NP07185	99600668MP	41802	DREP	DREP	20140602
0NP07185	99600668MP	51477	DREP	DREP	20141222
0VU43599	99600668MP	15572	DREP	DREP	20130227
0VU43599	99600668MP	29010	DREP	DREP	20130619
0VU43599	99600668MP	44477	DREP	DREP	20131114
0VU43599	99600668MP	59886	DREP	DREP	20140614
0VU43599	99600668MP	72938	DREP	DREP	20150325
8DN12628	99600668MP	74186	DREP	DREP	20100727
8DN12628	99600668MP	101807	DREP	DREP	20110919
8DN12628	99600668MP	126877	DREP	DREP	20120229
8DT43009	99600668MP	18829	DREP	DREP	20091215
8DT43009	99600668MP	18999	DREP	DREP	20100514
8DT43009	99600668MP	40864	DREP	DREP	20110912
8DT43009	99600668MP	50756	DREP	DREP	20111129
8DT43009	99600668MP	66477	DREP	DREP	20120822
8DT43009	99600668MP	77629	DREP	DREP	20130823
8DT43009	99600668MP	87800	DREP	DREP	20140805
8DT43009	99600668MP	90984	DREP	DREP	20150407

i would like to work out the difference between the

ADJKM

of the same bodyno and defect and also the difference berween (col1 refers to the date of event= age between last and second event)

once i have the differences i also want to compare these new vales to conditions where if age greater then 15 keep and adjkm is greater then 10000 keep.

please help.

[Steelers_In_DC]

Here's a solution, if want you mean by age is 15 days then you do not need the function, if you want month or years just replace day with what you want:

data have;

infile cards dsd;

length defect $10.;

informat col1 yymmdd8.;

format col1 mmddyy10.;

input BODYNO$ DEFECT$ ADJKM former_variable$ former_label $ COL1;

cards;

0NP07185,99600668MP,15332,DREP,DREP,20130116

0NP07185,99600668MP,29407,DREP,DREP,20131029

0NP07185,99600668MP,41802,DREP,DREP,20140602

0NP07185,99600668MP,51477,DREP,DREP,20141222

0VU43599,99600668MP,15572,DREP,DREP,20130227

0VU43599,99600668MP,29010,DREP,DREP,20130619

0VU43599,99600668MP,44477,DREP,DREP,20131114

0VU43599,99600668MP,59886,DREP,DREP,20140614

0VU43599,99600668MP,72938,DREP,DREP,20150325

8DN12628,99600668MP,74186,DREP,DREP,20100727

8DN12628,99600668MP,101807,DREP,DREP,20110919

8DN12628,99600668MP,126877,DREP,DREP,20120229

8DT43009,99600668MP,18829,DREP,DREP,20091215

8DT43009,99600668MP,18999,DREP,DREP,20100514

8DT43009,99600668MP,40864,DREP,DREP,20110912

8DT43009,99600668MP,50756,DREP,DREP,20111129

8DT43009,99600668MP,66477,DREP,DREP,20120822

8DT43009,99600668MP,77629,DREP,DREP,20130823

8DT43009,99600668MP,87800,DREP,DREP,20140805

8DT43009,99600668MP,90984,DREP,DREP,20150407

;

run;

proc sort data=have;by bodyno defect;

data all want;

set have;

format l_col1 mmddyy10.;

by bodyno defect;

l_adjkm = lag(adjkm);

l_col1 = lag(col1);

if not first.bodyno then do;

diff_adjkm = adjkm - l_adjkm;

diff_date = intck('day',l_col1,col1);

end;

output all;

if diff_adjkm > 10000 and diff_date > 15 then output want;

run;

[Kana]

hi second part to the question change add and delete a cloumn.

new data

PROJECT	DEFECT	ADJKM	NAME OF FORMER	LABEL OF	COL1
			VARIABLE	FORMER VARIABLE
E65	99600668MP	18829	DREP	DREP	20091215
E65	99600668MP	18999	DREP	DREP	20100514
E65	99600668MP	40864	DREP	DREP	20110912
E65	99600668MP	50756	DREP	DREP	20111129
E65	99600668MP	66477	DREP	DREP	20120822
E65	99600668MP	77629	DREP	DREP	20130823
E65	99600668MP	87800	DREP	DREP	20140805
E65	99600668MP	90984	DREP	DREP	20150407
E65	99600668MP	74186	DREP	DREP	20100727
E65	99600668MP	101807	DREP	DREP	20110919
E65	99600668MP	126877	DREP	DREP	20120229
E82	99600668MP	15572	DREP	DREP	20130227
E82	99600668MP	29010	DREP	DREP	20130619
E82	99600668MP	44477	DREP	DREP	20131114
E82	99600668MP	59886	DREP	DREP	20140614
E82	99600668MP	72938	DREP	DREP	20150325
F30	99600668MP	15332	DREP	DREP	20130116
F30	99600668MP	29407	DREP	DREP	20131029
F30	99600668MP	41802	DREP	DREP	20140602
F30	99600668MP	51477	DREP	DREP	20141222

i want to select the first defect accross all the ranges and work out the average MILEAGE OF THE FIRST DEFECT. same for the second DEFECT AND ITS MILEAGE and the third until the 4th.

1st	2nd	3rd	4th
F30	avg km	avg km	avg km	avg km
E82	avg km	avg km	avg km	avg km

how can i do this this such that i have a layout like like the one below.

[Kana]

Can Anyone help me please

[LinusH]

First, this is not a service. It would be decent to try to start this task and ask for more specific help.

Is this a report?

Also, add real numbers to your sample output so we better can understand the business rule.

Basically it looks like you wish to get the first four defects for each project. How do we calculate the average?

[RW9]

You haven't actually asked a question, more requested someone write the program for you.  What have you tried?  Attempt to do it yourself, then if you have any specific questions ask them, and provide test data in a way that we don't have to type it all in (i.e. datastep) and with required output.  As for getting an output something like yours there is numerous ways of doing it, maybe something along the lines of

proc transpose data=have out=t_have;

     by project;

     var adjkm;

run;