how to do

[thanikondharish]

data s ;
input studyid $ status $12. seq ;
cards;
1001 screened 1
1001 randomized 2
1001 randomized 2
1002 screened 1
1002 complete 3
1003 screened 1
1003 non-complete 3
1003 non-complete 3
1004 screened 1
1004 screened 1
1005 screefailure 1
1005 screefailure 1
;

studyid	status	seq	work
1001	randomized	2	randomized
1001	randomized	2	randomized
1001	screened	1	randomized
1002	complete	3	complete
1002	screened	1	complete
1003	non-complete	3	non-comple
1003	non-complete	3	non-comple
1003	screened	1	non-comple
1004	screened	1	screened
1004	screened	1	screened
1005	screefailure	1	screefailu
1005	screefailure	1	screefailu

how to do convert s datasset like above

[PeterClemmensen]

Please be more specific about the logic here.

How is the work variable determined?

[Kurt_Bremser]

Show the code you already tried.

[RW9]

Assign a specific order to the visits, so that they are in the sequence you need, then sort by that, and retain the first one, e.g.

data inter;
  set s;
  select (status);
    when ("screened") then 3;
    when ("randomised") then 1;
    when ("non-complete") then 2;
    otherwise;
  end;
run;

proc sort data=inter;
  by studyid status;
run;
 
data inter;
  set inter;
  by studyid;
  if first.studyid;
run;

data want;
  merge s inter;
  by studyid;
run;

I assume we wont hear back on this topic, or see any of your posts responded to or marked correct?

[mkeintz]

It appears that you want to apply the last value of work for each id to all the preceding records for the same id.  If so:

data want;
  set have;
  by id;
  if last.id then do until (last.id);
    set have (drop=work);
    by id;
    output;
  end;
run;