Hi SMEs,
I am seeking your help to resolve the issues I am facing with the derived visit.
I have attached my_output.xlsx and i_need.xlsx.
The file my_output.xlsx was generated using the SAS code below; however, I need the output to match i_need.xlsx.
Logic implemented:
Week 1 to Week 12 (weekly): when 1 < ADY ≤ 84
Week 1 is derived by taking the worst AVAL from Day 1 to Day 7
Continue similarly up to Week 12 (Day 78 to Day 84)
Week 15 to End of Treatment day. (every 3 weeks): when 85 ≤ ADY ≤ EOTDY
Week 15 is derived by taking the worst AVAL from Day 85 to Day 105. Repeat this process every 3 weeks until the end of treatment day. (EOTDY).
The above logic is used to derive the worst value per USUBJID / PARAMCD / AVISITN.
For these derived records, DTYPE = WOCF is assigned and appended to the original dataset.
I have created a new variable, NEW_AVISITN, to implement this logic without affecting the existing AVISITN. However, I am not getting the expected values.
Example: I am getting two records for this below PARMCD and avisitn.
| 101 | ABCD02W | 2025-03-14T16:56:00 | 9 | Week 9 | 57 | 354 | WOCF | Slightly severe | 2 |
| 101 | ABCD02W | 2025-03-16T18:02:00 | 9 | Week 9 | 59 | 354 | WOCF | Severe | 3 |
| 101 | ABCD02W | 2025-04-04T17:45:00 | 12 | Week 12 | 78 | 354 | WOCF | Not severe | 1 |
| 101 | ABCD02W | 2025-04-05T18:46:00 | 12 | Week 12 | 79 | 354 | WOCF | Slightly severe | 2 |
data test1;
set test;
by usubjid avisitn;
retain max_ady;
/* Calculate maximum ADY for each subject on first observation */
if first.usubjid then do;
max_ady = .;
end;
/* Track the maximum ADY value */
if not missing(ady) then do;
max_ady = max(max_ady, ady);
end;
/* Derive visit number and visit name for every observation */
if not missing(ady) then do;
eotdy_value = coalesce(eotdy, max_ady);
if 1 < ady <= 84 then do;
/* Week 1 to Week 12 (every week) when 1 < ADY <= 84 */
/* Day 1-7 = Week 1, Day 8-14 = Week 2, ..., Day 78-84 = Week 12 */
new_avisitn = ceil((ady - 1) / 7);
new_avisit = catx(" ", "Week", put(new_avisitn, best.));
end;
else if not missing(eotdy_value) and 85 <= ady <= eotdy_value then do;
/* After Week 12 every 3 weeks when 85 <= ADY <= EOTDY */
/* Day 85-105 = Week 15, Day 106-126 = Week 18, Day 127-147 = Week 21, etc. */
/* Formula: Week = 15 + floor((ady - 85) / 21) * 3 */
new_avisitn = 15 + floor((ady - 85) / 21) * 3;
new_avisit = catx(" ", "Week", put(new_avisitn, best.));
end;
else do;
call missing(new_avisitn, new_avisit);
end;
end;
else do;
call missing(new_avisitn, new_avisit);
end;
output;
run;
%macro wocf(paramcd, paramcd_w);
proc sql;
create table ¶mcd_w._ as
select * from test1
where not missing(avalc) and paramcd = "¶mcd"
order by usubjid, new_avisitn, aval descending;
quit;
data ¶mcd_w._1;
set ¶mcd_w._;
by usubjid new_avisitn descending aval;
if first.new_avisitn;
run;
data ¶mcd_w._2;
length param $200.;
set ¶mcd_w._1;
dtype = "WOCF";
param = "¶m_desc";
PARAMCD = "¶mcd_w";
PARAMTYP = "DERIVED";
drop aval;
run;
%mend wocf;
/* Call the macro for each parameter */
%wocf(ABCD0102, ABCD02W);
%wocf(ABCD0103, ABCD03W);
%wocf(ABCD0105, ABCD05W );
%wocf(ABCD0106, ABCD06W);
data derived;
set ABCD02W_2 ABCD03W_2 ABCD05W_2 ABCD06W_2;
run;
**adding derived records to the original data set**
data combi;
set test1 derived;
run;
Could you please let me which mistake I am doing here or please provide me a simple solution for this problem.
