Output row based on a condition by SUBJID

[JennieWalker]

Hi all

I am seeking advice on how to output a row based on the AVLAC value by SUBJID.  Each subject has 3 rows corresponding to different visit days. I would like to output the first row (earliest ADY)  by SUBJID when AVALC= “Y”. If this condition is met, I would like to move to the next subject. If the condition is not met, then I would like to output a row where AVLAC= “N”.

For example, using the below dataset I would like to output three rows - one for each subject.

SUBJID	ADY	AVALC	PARAMCD	AVISIT
1	7	N	GOAL	WEEK 1
1	14	Y	GOAL	WEEK 2
1	21	N	GOAL	WEEK 3
2	7	Y	GOAL	WEEK 1
2	14	Y	GOAL	WEEK 2
2	21	N	GOAL	WEEK 3
3	7	N	GOAL	WEEK 1
3	14	N	GOAL	WEEK 2
3	21	N	GOAL	WEEK 3

Desired output...

SUBJID	ADY	AVALC	PARAMCD	AVISIT
1	14	Y	GOALMET	POSTBASE BEST VALUE
2	7	Y	GOALMET	POSTBASE BEST VALUE
3	.	N	GOALMET	POSTBASE BEST VALUE

So far my code falls short – with the below code I am outputting all rows where AVALC= “Y”.

data ds2; 
  do until (last.SUBJID);
  set ds1;

  by SUBJID;

    if AVALC="Y" then do;
      PARAMCD="GOALMET";
      AVISIT="POSTBASE BEST VALUE";
    output;
    end;

  end;
run;

Thanks in advance for your time!

Jennie

[ballardw · Accepted solution]

Please post data as data step code. Or at least plain text. What ever source you copy those values from becomes impossible to use in  creating data step code without retyping everything and I'm too lazy to do that.

Why does the value of ADY become missing for Subjid 3?

If the value of Paramcd and Avisit in the output are exactly the same for all observations why bother to have them? Note that without actual data I suspect that the length of Avisit may very well not be long enough to hold the value "POSTBASE BEST VALUE" and quite possibly the same for Paramcd.

I think this matches your selection logic. Note this requires the input data set to be sorted by Subjid and Avisit.

data want;
   set have;
   by subjid;
   retain selected;
   if first.subjid the selected=0;
   if avalc='Y' and selected=0 then do;
      /* attempt to set Paramcd and Avisit if needed 
         goes here
      */
      output;
      selected=1;
   end;
   if last.subjid and selected=0 then do;
      /* attempt to set Paramcd and Avisit if needed 
         goes here
      */      
      output;
   end;
   drop selected;
run;

When data is used with By group processing SAS provides automatic temporary variables First. and Last. for each variable on the By statement. These are 1/0 valued for true/false so can be used to do things conditionally for the first or last observation of a group. In this case we are setting a flag for a Retained variable, the value will be kept across the data step boundary so keeps whether we have already a selected a value for the Subjid to output. If we get to the last of the Subjid and have not selected a record for output based on Avalc then it outputs the last record for the Subjid.

I did not include code to reset values for Paramcd and Avisit because of the comments I made above. Replace the comments with assignments to do so. I also did not assign a value for ADY because no rule was provided to such when Avalc was never Y

[JennieWalker]

Fantastic code! Thank you so much.