topic Re: Assign Flag as minimum value , if same value then last records flag in SAS Programming

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

raja777pharma — Sat, 01 Mar 2025 06:44:17 GMT

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.

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

Ksharp — Sat, 01 Mar 2025 07:45:40 GMT

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;

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

raja777pharma — Sat, 01 Mar 2025 08:47:48 GMT

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.

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

quickbluefish — Sat, 01 Mar 2025 15:32:45 GMT

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

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

raja777pharma — Sat, 01 Mar 2025 15:38:26 GMT

Hi ,

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

Thank you,

Raja

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

Ksharp — Sun, 02 Mar 2025 01:23:35 GMT

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;

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

mkeintz — Mon, 03 Mar 2025 01:16:14 GMT

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:

Data are sorted by USUBJID/PARAMCD/AVISITN.
Records come in groups of three consecutive observations.
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.