BookmarkSubscribeRSS Feed
Kana
Calcite | Level 5

hi there

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

BODYNODEFECTADJKMNAME OF FORMERLABEL OFCOL1
VARIABLEFORMER VARIABLE
0NP0718599600668MP15332DREPDREP20130116
0NP0718599600668MP29407DREPDREP20131029
0NP0718599600668MP41802DREPDREP20140602
0NP0718599600668MP51477DREPDREP20141222
0VU4359999600668MP15572DREPDREP20130227
0VU4359999600668MP29010DREPDREP20130619
0VU4359999600668MP44477DREPDREP20131114
0VU4359999600668MP59886DREPDREP20140614
0VU4359999600668MP72938DREPDREP20150325
8DN1262899600668MP74186DREPDREP20100727
8DN1262899600668MP101807DREPDREP20110919
8DN1262899600668MP126877DREPDREP20120229
8DT4300999600668MP18829DREPDREP20091215
8DT4300999600668MP18999DREPDREP20100514
8DT4300999600668MP40864DREPDREP20110912
8DT4300999600668MP50756DREPDREP20111129
8DT4300999600668MP66477DREPDREP20120822
8DT4300999600668MP77629DREPDREP20130823
8DT4300999600668MP87800DREPDREP20140805
8DT4300999600668MP90984DREPDREP20150407

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.

5 REPLIES 5
Steelers_In_DC
Barite | Level 11

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
Calcite | Level 5

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

new data

PROJECTDEFECTADJKMNAME OF FORMERLABEL OFCOL1
VARIABLEFORMER VARIABLE
E6599600668MP18829DREPDREP20091215
E6599600668MP18999DREPDREP20100514
E6599600668MP40864DREPDREP20110912
E6599600668MP50756DREPDREP20111129
E6599600668MP66477DREPDREP20120822
E6599600668MP77629DREPDREP20130823
E6599600668MP87800DREPDREP20140805
E6599600668MP90984DREPDREP20150407
E6599600668MP74186DREPDREP20100727
E6599600668MP101807DREPDREP20110919
E6599600668MP126877DREPDREP20120229
E8299600668MP15572DREPDREP20130227
E8299600668MP29010DREPDREP20130619
E8299600668MP44477DREPDREP20131114
E8299600668MP59886DREPDREP20140614
E8299600668MP72938DREPDREP20150325
F3099600668MP15332DREPDREP20130116
F3099600668MP29407DREPDREP20131029
F3099600668MP41802DREPDREP20140602
F3099600668MP51477DREPDREP20141222

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 2nd3rd4th
F30 avg km avg km avg km avg km
E82avg 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
Calcite | Level 5

Can Anyone help me please

LinusH
Tourmaline | Level 20

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?

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1103 views
  • 0 likes
  • 4 in conversation