topic Re: Set a Flag for last non-missing Value in SAS Programming

Set a Flag for last non-missing Value

sas_mania — Thu, 12 Jul 2018 07:28:10 GMT

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;

Re: base sas

andreas_lds — Thu, 12 Jul 2018 06:58:55 GMT

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?

Re: Set a Flag for last non-missing Value

Kurt_Bremser — Thu, 12 Jul 2018 07:28:36 GMT

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;

Re: Set a Flag for last non-missing Value

Kurt_Bremser — Thu, 12 Jul 2018 07:30:15 GMT

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.

Re: Set a Flag for last non-missing Value

Astounding — Thu, 12 Jul 2018 12:51:58 GMT

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.

Re: Set a Flag for last non-missing Value

Ksharp — Thu, 12 Jul 2018 13:55:04 GMT

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;