topic Re: Data Step - Do Loop Question in SAS Programming

Data Step - Do Loop Question

NJCroMag — Thu, 09 Jan 2014 15:59:06 GMT

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.

Re: Data Step - Do Loop Question

ArvindM — Thu, 09 Jan 2014 16:11:49 GMT

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;

Re: Data Step - Do Loop Question

overmar — Thu, 09 Jan 2014 16:19:12 GMT

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

Re: Data Step - Do Loop Question

AmanSAS — Fri, 10 Jan 2014 02:54:49 GMT

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;