01-09-2014 10:59 AM
I have a dataset with three columns in it:
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.
01-09-2014 11:11 AM
Below mentioned code will give you expected result (provided you have dataset sorted with contract_no and age in descending:
if first.contract_no then new_field = age;
01-09-2014 11:19 AM
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:
create table wage as
select contract_no, first_name, age, (max(age)) as max_age
group by contract_no;
Hope this helps,