How to retain the maximum flag value ("Y" over "N" over missing)

Reply
Occasional Contributor
Posts: 7

How to retain the maximum flag value ("Y" over "N" over missing)

The main purpose of the variable ABNOLBFL is to highlight the existence of at least one abnormal lab test value that ocurred after the beginning of the treatment (adt>trtsdt). ABNOLBFL should have the same value across all rows of the same subjid and paramcd. These are the rules:

If a patient had at least one abnormal value after the beginning of the treatment, keep "Y".

If the patient did not have any abnormal value but there is at least one value after the beginning of the treatment, keep "N"

Oherwise, leave as missing.

This is my code prior to keeping the last subjid/paramcd observation (which in this case should have ABNOLBFL = "Y")  and below is the failed outcome. I would appreciate your suggestions. I'd like to keep using retain, if possible. Thank you!

data x;

    input subjid lbseq paramcd $ adt trtsdt anrind $;

    datalines;

11002 100 HGBA02C 19836 20031 NORMAL

11002 150 HGBA02C 20045 20031 NORMAL   

11002 167 HGBA02C 20104 20031 HIGH   

11002 182 HGBA02C 20119 20031 NORMAL   

;

run;

   

proc sort data = x;

    by subjid paramcd adt;

run;

data y;

    set x;

    by subjid paramcd adt;

    retain abnolbfl;

    if first.paramcd then abnolbfl = "";

    if trtsdt >= adt > . then abnolbfl = "";

    else    if index(anrind,"x")=0 and index(anrind,"HIGH")=0 and index(anrind,"LOW")=0

            and adt > trtsdt > . then abnolbfl = "N";

    else if  (index(anrind,"x")>0 or index(anrind,"HIGH")>0 or index(anrind,"LOW")>0)

            and adt > trtsdt > . then abnolbfl = "Y";

proc print;

run;

OUTCOME:

Obs    subjid    lbseq    paramcd     adt     trtsdt    anrind     abnolbfl

1      11002     100     HGBA02C    19836     20031    NORMAL            

2      11002     150     HGBA02C    20045     20031    NORMAL           N   

3      11002     167     HGBA02C    20104     20031    HIGH                 Y   

4      11002     182     HGBA02C    20119     20031    NORMAL           N    <----  I would like this value to be "Y"

ab

Super User
Posts: 5,362

Re: How to retain the maximum flag value ("Y" over "N" over missing)

Your code is starting off just fine, sorting and using a BY statement in the DATA step. 

From there, much of it is questionable.  Why use the INDEX function at all?  Are you expecting that you might find unusual data values such as "HIGH VALUE" instead of "HIGH"?  Or that "HIGH" might actually have leading blanks such as "   HIGH"?  And what would a value of "xxxx" mean?

Not having answers to these questions, I'll make a few assumptions.  Assuming that all ANRIND values are left-hand justified, and that "xxxxx" can be ignored, here is one ending to your DATA step:

if anrind in ('HIGH', 'LOW') then abnolbfl='Y';

else if anrind='NORMAL' and abnobfl=' ' then abnolbfl='N';

Even if this doesn't match exactly the logic you need for your particular data set, it should be close enough that you can work with it.

Good luck.

Super User
Super User
Posts: 6,844

Re: How to retain the maximum flag value ("Y" over "N" over missing)

You should use a double DOW loop.  First loop over all of the records for the group and set the flag. Then loop again and output the records.

data x;

    input subjid lbseq paramcd $ adt trtsdt anrind $;

    datalines;

11002 100 HGBA02C 19836 20031 NORMAL

11002 150 HGBA02C 20045 20031 NORMAL  

11002 167 HGBA02C 20104 20031 HIGH  

11002 182 HGBA02C 20119 20031 NORMAL  

11001 100 HGBA02C 19836 20031 NORMAL

11001 150 HGBA02C 20045 20031 NORMAL  

11003 167 HGBA02C 20104 20031 LOW  

11004 182 HGBA02C 20119 20031 NORMAL  

11005 182 HGBA02C 20119 20031 .

;

run;

  

proc sort data = x;

    by subjid paramcd adt;

run;

data y;

  do until (last.paramcd) ;

    set x;

    by subjid paramcd adt;

    if first.paramcd then abnolbfl = ' ';

    if anrind in ('HIGH','LOW') then abnolbfl = 'Y';

    else if anrind = 'NORMAL' and abnolbfl = ' ' then abnolbfl='N';

  end;

  do until (last.paramcd) ;

    set x;

    by subjid paramcd adt;

    output;

  end;

run;

proc print;

run;

Occasional Contributor
Posts: 7

Re: How to retain the maximum flag value ("Y" over "N" over missing)

Thanks Astounding and Tom.

Astounding, thanks for your suggestions. Yes, I want to keep the index given some data issues but we can ignore them for practical purposes (it's not part of the doubt). About the 'x', my apologies, it should be 'ABNORMAL'.

                         else if index(anrind,"ABNORMAL")=0 and index(anrind,"HIGH")=0 and index(anrind,"LOW")=0

                         and adt > trtsdt > . then abnolbfl = "N";


Tom, thanks so much! that is what I was looking for. Here is the output with your data:

lbseq    paramcd     adt trtsdt    anrind    abnolbfl

  1      11001     100 HGBA02C    19836     20031 NORMAL N   

2      11001     150 HGBA02C    20045     20031 NORMAL N   

3      11002     100 HGBA02C    19836     20031 NORMAL Y   

4      11002     150 HGBA02C    20045     20031 NORMAL Y   

5      11002     167 HGBA02C    20104     20031 HIGH   Y   

6      11002     182 HGBA02C    20119     20031 NORMAL Y   

7      11003     167 HGBA02C    20104     20031 LOW    Y

8      11004     182 HGBA02C    20119     20031 NORMAL N   

  9      11005     182 HGBA02C    20119     20031                     

Just to have it really clear, is RETAIN not useful for my purpose?

Thanks,

Claudia

Ask a Question
Discussion stats
  • 3 replies
  • 289 views
  • 6 likes
  • 3 in conversation