Hey folks ,here is my data and i want to flag the last non missing obs before week2 ,suggest a method other than subsetting and sorting
DATA LAB;
INPUT USUBJID 3. VISIT $10. @15 LBTESTCD $3.
@19 LBORRES 3.
@23 LBDTC DDMMYY10.;
FORMAT LBDTC DDMMYY10.;
DATALINES;
101 SCREENING ALT 24 24-10-2015
101 PREDOSE ALT 52 22-11-2015
101 BASELINE ALT 45 22-12-2015
101 WEEK2 ALT 50 10-01-2016
101 WEEK4 ALT 45 11-02-2016
101 WEEK6 ALT 55 12-02-2016
101 SCREENING LDH 110 24-10-2015
101 PREDOSE LDH . 22-11-2015
101 BASELINE LDH . 23-12-2015
101 WEEK2 LDH 120 10-01-2016
101 WEEK4 LDH 120 11-02-2016
102 SCREENING ALT 44 24-10-2015
102 PREDOSE ALT 55 22-11-2015
102 BASELINE ALT . 10-01-2016
102 WEEK2 ALT 52 11-02-2016
102 SCREENING LDH . 24-10-2015
102 PREDOSE LDH . 22-11-2015
102 BASELINE LDH 120 12-12-2015
102 WEEK2 LDH 100 11-01-2016
102 WEEK4 LDH 110 12-02-2016
;
RUN;
Unfortunately you have - again - not paid attention to the important hints displayed below the "Post" button:
Please fix the issues in your post, especially the title.
Why do you want to solve this without subsetting and sorting?
The goal determines the tools:
data lab;
input
usubjid :3.
visit :$10.
lbtestcd :$3.
lborres :3.
lbdtc :ddmmyy10.
;
format lbdtc ddmmyy10.;
datalines;
101 SCREENING ALT 24 24-10-2015
101 PREDOSE ALT 52 22-11-2015
101 BASELINE ALT 45 22-12-2015
101 WEEK2 ALT 50 10-01-2016
101 WEEK4 ALT 45 11-02-2016
101 WEEK6 ALT 55 12-02-2016
101 SCREENING LDH 110 24-10-2015
101 PREDOSE LDH . 22-11-2015
101 BASELINE LDH . 23-12-2015
101 WEEK2 LDH 120 10-01-2016
101 WEEK4 LDH 120 11-02-2016
102 SCREENING ALT 44 24-10-2015
102 PREDOSE ALT 55 22-11-2015
102 BASELINE ALT . 10-01-2016
102 WEEK2 ALT 52 11-02-2016
102 SCREENING LDH . 24-10-2015
102 PREDOSE LDH . 22-11-2015
102 BASELINE LDH 120 12-12-2015
102 WEEK2 LDH 100 11-01-2016
102 WEEK4 LDH 110 12-02-2016
;
run;
proc sort data=lab;
by usubjid descending lbdtc;
run;
data want;
set lab;
by usubjid;
retain check;
flag = 0;
if first.usubjid then check = .;
if visit = 'WEEK2' then check = 0;
else if lborres ne . and check = 0
then do;
check = 1;
flag = 1;
end;
drop check;
run;
proc sort data=want;
by usubjid lbdtc;
run;
PS I edited the inadequate subject line. Please be more creative in the future.Using "base sas" in the Base SAS Programming community is, ahem, not very bright.
OK, no sorting but assuming that your data is already sorted as illustrated ...
Assuming you want a separate flag for each lab code:
data want;
week2_found = 'N';
recnum = 0;
do until (last.lbtestcd);
set have;
by usubjid lbtestcd notsorted;
if visit='WEEK2' then week2_found = 'Y';
if week2_found = 'N' then recnum + 1;
end;
recnum2=0;
do until (last,.lbtestcd);
set have;
by usubjid lbtestcd notsorted;
recnum2 + 1;
if recnum = recnum2 then flag=1;
else flag=0;
output;
end;
drop recnum recnum2 week2_found;
run;
It's untested code but looks about right.
How about this one ?
data lab;
input
usubjid :3.
visit :$10.
lbtestcd :$3.
lborres :3.
lbdtc :ddmmyy10.
;
format lbdtc ddmmyy10.;
datalines;
101 SCREENING ALT 24 24-10-2015
101 PREDOSE ALT 52 22-11-2015
101 BASELINE ALT 45 22-12-2015
101 WEEK2 ALT 50 10-01-2016
101 WEEK4 ALT 45 11-02-2016
101 WEEK6 ALT 55 12-02-2016
101 SCREENING LDH 110 24-10-2015
101 PREDOSE LDH . 22-11-2015
101 BASELINE LDH . 23-12-2015
101 WEEK2 LDH 120 10-01-2016
101 WEEK4 LDH 120 11-02-2016
102 SCREENING ALT 44 24-10-2015
102 PREDOSE ALT 55 22-11-2015
102 BASELINE ALT . 10-01-2016
102 WEEK2 ALT 52 11-02-2016
102 SCREENING LDH . 24-10-2015
102 PREDOSE LDH . 22-11-2015
102 BASELINE LDH 120 12-12-2015
102 WEEK2 LDH 100 11-01-2016
102 WEEK4 LDH 110 12-02-2016
;
run;
data want;
do i=1 by 1 until(last.lbtestcd);
set lab;
by usubjid lbtestcd notsorted;
if visit not =: 'WEEK' and not missing(lborres) then idx=i;
end;
do i=1 by 1 until(last.lbtestcd);
set lab;
by usubjid lbtestcd notsorted;
if idx=i then flag=1;else flag=0;
output;
end;
drop i idx;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: