Specifying Conditions for Subjects with Multiple Rows

[daszlosek]

Hello Eveyrone,

I am trying to satsify the following conditions in my dataset:

• If there are multiple ECHO results, pick only the adequate result(s), if all results are inadequate pick one closest to Day 35
• If there are multiple adequate results, pick only the one(s) in the prespecified window (Day 32-42)
• If there are multiple adequate results in the window, and they have the same result (MI, no MI, or Inadequate) pick the one closest to Day 35
• If there are multiple adequate results in the window, and they have different results (one MI and one no MI), choose the DVT (but Annemarie thinks this did not happen)

And my data looks like this:

SUBJID	ECHORES	WINDOW
1	No MI	35
2	Inadequate	34
2	No_MI	41
3	Yes	32
3	Yes	34
4	Yes	32
4	Yes	28

I was starting by working on the first condition by trying out a series of do loops:

data want;
do until(last.usubjid);
    set test; by usubjid;
	 if first.usubjid then do;
          if  ECHORES = "No MI" or if ECHORES = "MI" THEN DO; 
              if last.usubjid then do;
                   if  ECHORES = "INADEQUATE" then delete;

				   end;
				   end;
				   end;
				   end;



run;

Is using DOW loops the right way to approach this?

Best,

Donald

[RW9]

Personally I would start with one datastep for each of your conditions.  Are they heirachical, it looks it from a glance at the logic.  So point 1 do a datastep, point 2 do a datastep.  Once you know the logic its easier to try to combine.  If you can provide some test data in the form of a datastep with data to cover each condition, and what the output should look like, can look further.

[daszlosek · Accepted solution]

Hello Everyone,

I actually found a totally different way of doing that I wanted to do selecting only the duplicates and then tranposing two datasets them erging them back together.

data have_dupes;
	set have;
	by subjid;
	if first.usubjid ne last.usubjid;
run;


proc transpose data=have_dupes out=wide1 ;
    by USUBJID ;
	var ECHORES;
run;

proc transpose data=have_dupes out=wide2 ;
    by USUBJID ;
	var ADY;
run;

DATA ADY;
SET wide2
(RENAME= (COL1 = ADY1 COL2 = ADY2));

KEEP USUBJID ADY1 ADY2;
RUN;

DATA ECHORES;
SET wide1
(RENAME= (COL1 = ECHORES1 COL2 = ECHORES2 ));

KEEP USUBJID ECHORES1 ECHORES2;
RUN;


data ady_ECHORES;
merge ady(in=a) ECHORES (in=b);
by usubjid;
IF ECHORES1 = "YES" ECHORES1 = "MI";
IF ECHORES2 = "YES" ECHORES2 = "MI";
run;




DATA SASISNEAT;
SET ady_ECHORES;
ADY3    =0;


IF ECHORES1 = "Inadequate" AND ECHORES2 = "Inadequate" THEN DO;
 IF ABS(ADY1 - 35) < ABS(ADY2 - 35) THEN ADY3 = ADY1; ELSE ADY3 = ADY2;
 ECHORES3 = "Inadequate";
END;


IF ECHORES1 = "No_MI" AND ECHORES2 = "No_MI" THEN DO;
 IF ABS(ADY1 - 35) < ABS(ADY2 - 35) THEN ADY3 = ADY1; ELSE ADY3 = ADY2;
 ECHORES3 = "No_MI";
END;

IF ECHORES1 = "MI" AND ECHORES2 = "MI" THEN DO;
 IF ABS(ADY1 - 35) < ABS(ADY2 - 35) THEN ADY3 = ADY1; ELSE ADY3 = ADY2;
 ECHORES3 = "MI";
END;

IF (ECHORES1 = "Inadequate" AND ECHORES2 = "MI") THEN ECHORES3 = "MI";
IF (ECHORES1 = "Inadequate" AND ECHORES2 = "MI") THEN ADY3     =  ADY2;

IF (ECHORES2 = "Inadequate" AND ECHORES1 = "MI") THEN ECHORES3 = "MI";
IF (ECHORES2 = "Inadequate" AND ECHORES1 = "MI") THEN ADY3     =  ADY1;


IF (ECHORES1 = "Inadequate" AND ECHORES2 = "No_MI") THEN ECHORES3 = "No_MI";
IF (ECHORES1 = "Inadequate" AND ECHORES2 = "No_MI") THEN ADY3     =     ADY2;



IF (ECHORES2 = "Inadequate" AND ECHORES1 = "No_MI") THEN ECHORES3 = "No_MI";
IF (ECHORES2 = "Inadequate" AND ECHORES1 = "No_MI") THEN ADY3     =     ADY1;


IF ADY3 >= 32 AND ADY3 <= 47 THEN WINDOWFL = "Y"; ELSE WINDOWFL = "N";
RUN;