Data Step - Do Loop Question

[NJCroMag]

I have a dataset with three columns in it:

CONTRACT_NO

FIRST_NAME

AGE

The dataset will always be sorted by CONTRACT_NO (ascending) and AGE (descending).

I need to create a new field that will contain the age of the oldest person within a specific contract.  Since the dataset is already sorted by AGE descending, I'm thinking some kind of loop that buckets the CONTRACT_NO and first age encountered within that contract could be used.  And, when a new CONTRACT_NO is encountered, it'll set the NEW_FIELD value accordingly.  Problem is, the DATA step examples I've seen so far aren't very helpful.

I would expect my data to look like this upon completion of the step (for sample purposes, the input dataset will only contain eight records):

CONTRACT_NO          FIRST_NAME          AGE         NEW_FIELD

001                              JOHN                    50               50

001                              KIM                       48               50

001                              DAWN                  14                50

001                              SAM                     11                50

002                              JEN                       64               64

002                              MIKE                    62                64

002                              SCOTT                  32               64

002                              LISA                     31               64

Any help would be appreciated.

[ArvindM]

Below mentioned code will give you expected result (provided you have dataset sorted with contract_no and age in descending:

data t2;

set t1;

by contract_no;

retain new_field;

if first.contract_no then new_field = age;

run;

[overmar]

Or you could do it in Proc Sql without having to worry about sorting and it will just take the maximum age of each distinct a contract number and report it as max_age:

Proc sql;

create table wage as

select contract_no, first_name, age, (max(age)) as max_age

from dataset

group by contract_no;

quit;

Hope this helps,

Robert

[AmanSAS]

Try this

proc sql;

CREATE TABLE ONE AS

select CONTRACT_NO, FIRST_NAME,  AGE, max(AGE) as max_age

from dataset

group by CONTRACT_NO;

quit;