Assign Flag as minimum value , if same value then last records flag

[raja777pharma]

Hi Team , 

I am not getting flag correctly 

Below is data 

USUBJID	PARAMCD	ADTM	ADY	AVISITN	REPNUM
3001	EG	11JAN2024:12:12:37	29	7	1
3001	EG	11JAN2024:12:13:30	29	7	2
3001	EG	11JAN2024:12:14:07	29	7	3
3001	EG	11JAN2024:14:02:35	29	7	1
3001	EG	11JAN2024:14:03:12	29	7	2
3001	EG	11JAN2024:14:03:50	29	7	3
3002	EG	25JAN2023:14:02:22	169	7	1
3002	EG	25JAN2023:14:05:28	169	7	2
3002	EG	25JAN2023:14:08:28	169	7	3
3002	EG	01FEB2023:13:55:43	176	7	1
3002	EG	01FEB2023:13:58:46	176	7	2
3002	EG	01FEB2023:14:01:47	176	7	3

In Above i have sorted the data by USUBJID PARAMCD AVISITN ADTM REPNUM.

I am try to flag minmum ADY three sets of (REPNUM) in same AVISITN , if same ADY for Two sets of REPNUM in same AVISITN then Flag Last three records by ADY value .

Below is expecting want new_falg

USUBJID	PARAMCD	ADTM	ADY	AVISITN	REPNUM	NEW_FLAG
3001	EG	11JAN2024:12:12:37	29	7	1
3001	EG	11JAN2024:12:13:30	29	7	2
3001	EG	11JAN2024:12:14:07	29	7	3
3001	EG	11JAN2024:14:02:35	29	7	1	Y
3001	EG	11JAN2024:14:03:12	29	7	2	Y
3001	EG	11JAN2024:14:03:50	29	7	3	Y
3002	EG	25JAN2023:14:02:22	169	7	1	Y
3002	EG	25JAN2023:14:05:28	169	7	2	Y
3002	EG	25JAN2023:14:08:28	169	7	3	Y
3002	EG	01FEB2023:13:55:43	176	7	1
3002	EG	01FEB2023:13:58:46	176	7	2
3002	EG	01FEB2023:14:01:47	176	7	3

in Above first subject 3001 have same ADY for first 6 records , but need flag second set of REPNUM records 

For second subject  3002 have different ADY for two sets for REPNUM , so will flag minimum ADY that is ADY = 169 records will flag.

Thank you,

Raja.

[Ksharp]

data have;
infile cards truncover expandtabs;
input USUBJID	PARAMCD	$ ADTM	:$40. ADY	AVISITN	REPNUM;
cards;
3001	EG	11JAN2024:12:12:37	29	7	1
3001	EG	11JAN2024:12:13:30	29	7	2
3001	EG	11JAN2024:12:14:07	29	7	3
3001	EG	11JAN2024:14:02:35	29	7	1
3001	EG	11JAN2024:14:03:12	29	7	2
3001	EG	11JAN2024:14:03:50	29	7	3
3002	EG	25JAN2023:14:02:22	169	7	1
3002	EG	25JAN2023:14:05:28	169	7	2
3002	EG	25JAN2023:14:08:28	169	7	3
3002	EG	01FEB2023:13:55:43	176	7	1
3002	EG	01FEB2023:13:58:46	176	7	2
3002	EG	01FEB2023:14:01:47	176	7	3
;
data temp;
 set have;
 if REPNUM=1 then group+1;
run;
proc sql;
create table level_ady as
select usubjid,count(distinct ady) as n
 from have
  group by usubjid;
quit;
data temp2;
 merge temp level_ady;
 by usubjid;
run;
data want;
 do until(last.group);
  set temp2;
  by usubjid group;
  if first.usubjid then first=1;
  if last.usubjid then last=1;
 end;
 do until(last.group);
  set temp2;
  by usubjid group;
  if n=1 and last then new_flag='Y';
  if n>1 and first then new_flag='Y';
  output;
 end;
 drop n group first last;
 run;

[raja777pharma]

Hi Ksharp,

Thank you for code, however  i am not getting correctly when i have multiple PARAMCD and AVISITN.

In sample data i have only kept the one PARAMD='EG" and one AVISITN = 7 , but i have other PARAMCD's total 3 and AVISITN have more values  for each subject .

So aim is for each set of USUBJID,PARAMCD,AVISIT have only one set REPNUM flag, as mentioned for  each USUBJID,PARAMCD, AVISITN 

Above code is getting First REPNUM =1 records , but i need REPNUM = 1,2,3 records for each USUBJID,PARAMCD,AVISITN, which REPNUM set minimum ADY reccords and if same ADY in TWO sets of REPNUM then Last set REPNUM need.

Thank you,

Raja

Some time REPNUM is only less 2 records.

Thank you,

Raja.

[quickbluefish]

This is a pretty tricky problem.  It might help if we understood what the purpose of the NEW_FLAG variable is?

[raja777pharma]

Hi ,

This is to keep records only distinct by USUBJID,PARAMCD,AVSITN and REPNUM

Thank you,

Raja

[Ksharp · Accepted solution]

You need to post more data and output to explain your this complicated question.

Otherwise, we only could base on your data and guess what you are really looking for .

The following you could give  a try.

data have;
infile cards truncover expandtabs;
input USUBJID	PARAMCD	$ ADTM	:$40. ADY	AVISITN	REPNUM;
cards;
3001	EG	11JAN2024:12:12:37	29	7	1
3001	EG	11JAN2024:12:13:30	29	7	2
3001	EG	11JAN2024:12:14:07	29	7	3
3001	EG	11JAN2024:14:02:35	29	7	1
3001	EG	11JAN2024:14:03:12	29	7	2
3001	EG	11JAN2024:14:03:50	29	7	3
3002	EG	25JAN2023:14:02:22	169	7	1
3002	EG	25JAN2023:14:05:28	169	7	2
3002	EG	25JAN2023:14:08:28	169	7	3
3002	EG	01FEB2023:13:55:43	176	7	1
3002	EG	01FEB2023:13:58:46	176	7	2
3002	EG	01FEB2023:14:01:47	176	7	3
;
data temp;
 set have;
 if REPNUM=1 then group+1;
run;
proc sql;
create table level_ady as
select usubjid,PARAMCD,AVISITN,count(distinct ady) as n
 from have
  group by usubjid,PARAMCD,AVISITN;
quit;
data temp2;
 merge temp level_ady;
 by usubjid PARAMCD AVISITN;
run;
data want;
 do until(last.group);
  set temp2;
  by usubjid PARAMCD AVISITN group;
  if first.usubjid then first=1;
  if last.usubjid then last=1;
 end;
 do until(last.group);
  set temp2;
  by usubjid PARAMCD AVISITN group;
  if n=1 and last then new_flag='Y';
  if n>1 and first then new_flag='Y';
  output;
 end;
 drop n group first last;
 run;

[mkeintz]

This code flags the last-encountered triplet of observations that have any instance of ADY with the minimum value for a USUBJID/PARAMCD/AVISITN group.

The program assumes:

1. Data are sorted by USUBJID/PARAMCD/AVISITN.
2. Records come in groups of three consecutive observations.
3. Each such triplet starts with REPNUM=1 (and no other observation has REPNUM=1).

data have;
infile cards truncover expandtabs;
input USUBJID	PARAMCD	$ ADTM	:$40. ADY	AVISITN	REPNUM;
cards;
3001	EG	11JAN2024:12:12:37	29	7	1
3001	EG	11JAN2024:12:13:30	29	7	2
3001	EG	11JAN2024:12:14:07	29	7	3
3001	EG	11JAN2024:14:02:35	29	7	1
3001	EG	11JAN2024:14:03:12	29	7	2
3001	EG	11JAN2024:14:03:50	29	7	3
3002	EG	25JAN2023:14:02:22	169	7	1
3002	EG	25JAN2023:14:05:28	169	7	2
3002	EG	25JAN2023:14:08:28	169	7	3
3002	EG	01FEB2023:13:55:43	176	7	1
3002	EG	01FEB2023:13:58:46	176	7	2
3002	EG	01FEB2023:14:01:47	176	7	3
run;

data want (drop=_:);
  set have (in=firstpass)  have (in=secondpass);
  by usubjid paramcd avisitn;

  if first.avisitn then call missing(_rep_group,_min_ady,_min_group);
  retain _rep_group -- _min_group;

  if firstpass then do;
    if repnum=1 then _rep_group+1;
    _min_ady=min(ady,_min_ady);
    if ady=_min_ady then _min_group=_rep_group;
  end;

  if secondpass;
  if repnum=1 then _min_group=_min_group-1;
  if _min_group=0 then new_flag='Y';
run;

Each USUBJID/PARAMCD/AVISITN group is passed through twice.  The first time finds the minimum ADY and identifies the most recent triplet (_MIN_GROUP) having that minimum.  During the second pass _MIN_GROUP is decremented every time REPNUM=1 is encountered.  When it becomes zero, that's the group you want to flag.  After that group, the variable _MIN_GROUP will decrement to negative values for subsequent triplets.  These were determined NOT to have the minimum ADY in the first pass.