Flagging ID's based on the first observation in SAS

user_15500909 0
Viewed 102

I have a variable that shows a 0 for if the ID had a change, and . if it never changed. I arranged the dataset in ascending order by ID and descending order by the variable with the change. For example:

enter image description here

I want to flag the ID's that had a change occur. So I need a table that looks like:

enter image description here

I tried to use a do until statement by using first.ID to last.ID, but it didn't work.

sas
2 Answers

Just use RETAIN and FIRST. processing.

data want;
  set have;
  by id descending change ;
  if first.id then do;
     if change=0 then flag='Y';
     else flag='N';
  end;
  retain flag;
run;
user_49655490 
** Select all IDs with any change. Keep only one record per ID (the NODUPKEY option). **;
proc sort data=have (where=(change=0)) out=ids_with_change (keep=id) nodupkey; by id;

** Assign FLAG = Y if any change, else N **;
data want;
  merge have (in=in1)
        ids_with_change (in=in2)
        ;
  by id;
  
  select (in2);
    when (0) flag = 'N';
    when (1) flag = 'Y';
  end;

run;
user_43858300
Related