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.
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.