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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.