## retain

Occasional Contributor
Posts: 18

# retain

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

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.

Valued Guide
Posts: 863

## Re: retain

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_col1 = lag(col1);

if not first.bodyno then do;

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

end;

output all;

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

run;

Occasional Contributor
Posts: 18

## Re: retain

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.

Occasional Contributor
Posts: 18

Super User
Posts: 5,852

## Re: retain

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?

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

Data never sleeps
Super User
Posts: 9,427

## Re: retain

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;