Actually figured it out myself. Here is the code for anyone interested.   data want; do _n_ = 1 by 1 until(last.usubjid); set have; by usubjid; if visit ='Early Termination' and not(missing(aval)) then last_non_missing = aval; end; do _n_ = 1 to _n_; set have; aval =coalesce(aval,last_non_missing); output; end; drop last_non_missing; run; ... View more

Same approach, simply remove it from the Where and Drop statements like this   data have1; input SUBJID $ PARAMCD $ ADT :$9. VISITNUM :8. VISIT :$12. AVAL :8. ; infile datalines dlm = '|'; datalines; 1004|EASITOTA|21JUL2022|2|Day 1 |16.3 1004|EASITOTA|03AUG2022|4|Week 2 |8.6 1004|EASITOTA|17AUG2022|5|Week 4 |8.7 1004|EASITOTA|14SEP2022|6|Week 8 |9.6 ; run; data have2; input SUBJID $ ADT :$9. VISITNUM :8. VISIT :$12. RTT1L28D :8. RTT1G28D :8. SWREL :8. SWNREL :8. T2CM :8. RTT1L14D :8. RTT1G14D :8. NCUNODEV :8.; infile datalines dlm = '|'; datalines; 1004|21JUL2022|2|Day 1 |0|0|0|0|0|0|0|1 1004|03AUG2022|4|Week 2 |0|0|0|0|0|0|0|1 1004|17AUG2022|5|Week 4 |0|0|0|0|0|0|0|1 1004|14SEP2022|6|Week 8 |0|0|0|0|0|0|0|1 1004|13OCT2022|7|Week 12|0|0|0|0|0|0|0|1 1004|10NOV2022|8|Week 16|0|0|0|0|0|0|0|1 ; run; data want(drop = rc); format SUBJID PARAMCD ADT VISITNUM VISIT AVAL RTT1L28D RTT1G28D SWREL SWNREL T2CM RTT1L14D RTT1G14D NCUNODEV; if _N_ = 1 then do; dcl hash h(dataset : 'have1'); h.definekey('SUBJID', 'VISITNUM'); h.definedata(all : 'Y'); h.definedone(); end; set have2; if 0 then set have1; rc = h.find(); if rc then call missing(ADT, AVAL); run; ... View more

I'm still not sure I'm understanding.  But here's a bit of a stab at it, hopefully the approach will help.  I would usually approach LOCF by using RETAIN, rather than lag, something like:   data have; input PARAMCD $ USUBJID $ VISIT $ AVAL :8. ABLFL :$ AVISITN :8. RTT1L28D :8. SWNREL :8. T2SWREL :8.; infile datalines dlm = '|'; datalines; EASITOTA|1001|Screening|16| |10|0|0|0 EASITOTA|1001|Day 1 |13.4| |20|0|0|0 EASITOTA|1001|Week 2 |8.1| |30|0|0|0 EASITOTA|1001|Week 4 |5.6| |40|0|0|0 EASITOTA|1001|Week 8 |6| |50|0|0|0 EASITOTA|1001|Week 12 |8.7| |60|0|0|0 EASITOTA|1002|Screening|14| |10|0|0|0 EASITOTA|1002|Day 1 |16.4|Y|20|0|0|0 EASITOTA|1002|Week 2 |13.8| |30|0|0|0 EASITOTA|1002|Week 4 |21.4| |40|1|0|0 EASITOTA|1002|Week 8 |14| |50|1|0|0 EASITOTA|1002|Week 12 |15.6| |60|1|0|0 EASITOTA|1002|Week 16 |21.4| |70|1|0|0 EASITOTA|1004|Screening|9.6| |10|0|0|0 EASITOTA|1004|Week 2 |8.6| |30|0|0|0 EASITOTA|1004|Week 4 |8.7| |40|0|0|0 EASITOTA|1005|Screening|11.6| |10|1|0|0 EASITOTA|1005|Day 1 |19.8|Y|20|1|0|0 EASITOTA|1006|Screening|17.1| |10|0|0|0 EASITOTA|1006|Day 1 |15.8|Y|20|0|0|0 EASITOTA|1006|Unscheduled|6| |100|1|0|0 EASITOTA|1007|Screening|33.3| |10|0|0|0 EASITOTA|1007|Day 1 |40.8|Y|20|0|0|1 EASITOTA|1007|Week 2 |44| |30|0|0|1 EASITOTA|1007|Week 4 |48| |40|0|0|1 EASITOTA|1007|Unscheduled|57.8| |100|0|0|1 ; run; data want ; set have ; by USUBJID ; retain _RetainedAval ; if first.USUBJID then _RetainedAval=. ; if max(RTT1L28D,SWNREL,T2SWREL)=0 then _RetainedAval=Aval ; output ; PARAMCD='TOTEDPS1' ; Aval=_RetainedAval ; if SWNREL=1 then Aval=. ; if VISIT ne 'Unschedu' then output ; *drop _RetainedAval ; run ; proc print data=want ; run ;   ... View more

Thanks appreciated. yes that was just a typo with the USUBJID and no just one record per parameter with a value of 'Y' for ABLFL but that is good thinking. Here is the code I settled on and works fine.   proc sql; create table b1ind as select distinct usubjid ,paramcd ,a1ind from lbchg2 where ABLFL ='Y' order by 1; /* Merge b1ind back onto the original record*/ create table lbchg3 as select a.*, b.a1ind as b1ind from lbchg2 a left join b1ind b on a.usubjid=b.usubjid and a.paramcd=b.paramcd; quit;     ... View more

Sorry yes I guess I wasn't clear enough but after working through it this is the solution I wanted   data have; input USUBJID $ LBSTNRC :$40. LBNRIND :$14. AVALC :$200.; infile datalines dlm = '|'; datalines; 1001|Negative|NORMAL|Negative 1001|5.0, 6.0, 7.0, 8.0|NORMAL|6.0 1001|6.0, 7.0, 8.0, 9.0|NORMAL|7.0 1001|5.0, 6.0, 7.0, 8.0|NORMAL|5.0 1001|Negative|ABNORMAL|5 1001|None, Occasional, 1-2|NORMAL|Occasional 1001|None, Occasional, 1-5|NORMAL|None ; data want; length anrind $20.; set have; * ANRIND *; if lbstnrc ne '' and index(lbstnrc,',')>0 then do; if findw(lbstnrc,strip(avalc),', ',' ,')>0 then anrind ='Normal'; end; else anrind ='Abnormal'; run;     ... View more

Assuming the data set file is already grouped by USUBJID/PARAMCD, then:   data want; do until (last.paramcd); set have; by usubjid paramcd notsorted; if a1ind='High' or a2ind='Change high' then ANL03FL='Y'; end; do until (last.paramcd); set have; by usubjid paramcd notsorted; output; end; run; ... View more

This can be accomplished using SQL in-line views: proc sql; create table sum_by_totda as select a.usubjid ,'DACAPNT' as PARAMCD /* ,a.AVAL as Issued ,Returned ,Dispensed */ ,a.aval+Dispensed-Returned as AVAL from ( select usubjid, AVAL from sdtm.da where paramcd ="CAPSEXP" ) as a left join ( select usubjid, sum(dastresn) as Returned from sdtm.da where datestcd ="RETAMT" group by usubjid ) as b on a.usubjid=b.usubjid left join ( select usubjid, sum(dastresn) as Dispensed from sdtm.da where datestcd ="DISPAMT" group by usubjid ) as c on a.usubjid=c.usubjid ; quit; However, the calculation for AVAL specified doesn't make any sense to me. Here is what the values look like: usubjid PARAMCD Issued Returned Dispensed AVAL 1 DACAPNT 20 15 3 8 2 DACAPNT 20 15 3 8 3 DACAPNT 20 15 3 8 4 DACAPNT 20 15 1 6 5 DACAPNT 20 15 2 7   I would expect the final calculation to be AVAL-(Dispensed+Returned): proc sql; create table sum_by_totda as select a.usubjid ,'DACAPNT' as PARAMCD ,a.AVAL as Issued ,Returned ,Dispensed ,a.aval-(Dispensed+Returned) as AVAL from ( select usubjid, AVAL from sdtm.da where paramcd ="CAPSEXP" ) as a left join ( select usubjid, sum(dastresn) as Returned from sdtm.da where datestcd ="RETAMT" group by usubjid ) as b on a.usubjid=b.usubjid left join ( select usubjid, sum(dastresn) as Dispensed from sdtm.da where datestcd ="DISPAMT" group by usubjid ) as c on a.usubjid=c.usubjid ; quit; Which yields: usubjid PARAMCD Issued Returned Dispensed AVAL 1 DACAPNT 20 15 3 2 2 DACAPNT 20 15 3 2 3 DACAPNT 20 15 3 2 4 DACAPNT 20 15 1 4 5 DACAPNT 20 15 2 3 ... View more

Hello @smackerz1988,   If only "Y" and possibly "M" (but not "D") occur in your SUDUR values, it should be correct to round Y to 1/12 years (rather than 0.01). Y=round(D/365.25, 1/12); Note that "1 year, 6 months" are 1.5 years, not 1.6.   BTW, thanks for reminding me of the CALL IS8601_CONVERT routine. I had used it in 2012, but then almost forgot about it. ... View more