CODEWITHSUNDEEP
sas
sonarclick
sonarclick

Reputation: 73

Modifying SAS dataset variable based on conditions on other variables

I have a dataset in SAS EG that looks like this:

ID | Date | Name
ABC1 | 31MAR2016 | Text1
ABC1 | 30JUN2016 | Text2
ABC2 | 31MAR2016 | Text3
ABC2 | 30JUN2016 | Text4

I want to transform this dataset using a data step ideally to:

ID | Date | Name
ABC1 | 31MAR2016 | Text2
ABC1 | 30JUN2016 | Text2
ABC2 | 31MAR2016 | Text4
ABC2 | 30JUN2016 | Text4

So I want to pick the Name corresponding to the latest date and use it to replace the Name in the earliest date when IDs match.

Thanks in advance for your help.

Upvotes: 0

Views: 45

Answers (1)

Robert Soszyński
Robert Soszyński

Reputation: 1188

1) Sort data, the earliest records will be first in each ID group.

2) Using group processing change values for all recordsd except first in each group.

3) Sort to restore a original sequence.

proc sort data=source out=source_sorted;
    by id descending date;
run;

data result;
    set source_sorted;
    by id;
    length name_tmp $10;
    retain name_tmp "";

    if first.id then
        name_tmp = name;

    name = name_tmp;

    drop name_tmp;
run;

proc sort data=result;
    by id date;
run;

Upvotes: 1

Related Questions