DATA Step, Macro, Functions and more

Set a Flag for last non-missing Value

Reply
New Contributor
Posts: 2

Set a Flag for last non-missing Value

[ Edited ]

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;

 

Valued Guide
Posts: 571

Re: base sas

Posted in reply to sas_mania

Unfortunately you have - again - not paid attention to the important hints displayed below the "Post" button:

grafik.png

 

 

Please fix the issues in your post, especially the title.

 

Why do you want to solve this without subsetting and sorting?

Super User
Posts: 10,241

Re: Set a Flag for last non-missing Value

[ Edited ]
Posted in reply to sas_mania

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,241

Re: Set a Flag for last non-missing Value

Posted in reply to sas_mania

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,774

Re: Set a Flag for last non-missing Value

[ Edited ]
Posted in reply to sas_mania

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.

Super User
Posts: 10,778

Re: Set a Flag for last non-missing Value

[ Edited ]
Posted in reply to sas_mania

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;

 

Ask a Question
Discussion stats
  • 5 replies
  • 73 views
  • 0 likes
  • 5 in conversation