topic Re: Specifying Conditions for Subjects with Multiple Rows in SAS Programming

Specifying Conditions for Subjects with Multiple Rows

daszlosek — Thu, 17 Mar 2016 15:41:02 GMT

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

Re: Specifying Conditions for Subjects with Multiple Rows

RW9 — Thu, 17 Mar 2016 16:26:37 GMT

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.

Re: Specifying Conditions for Subjects with Multiple Rows

daszlosek — Fri, 18 Mar 2016 14:46:36 GMT

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;