DATA Step, Macro, Functions and more

Specifying Conditions for Subjects with Multiple Rows

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Specifying Conditions for Subjects with Multiple Rows

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

 


Accepted Solutions
Solution
‎03-18-2016 10:47 AM
Frequent Contributor
Posts: 76

Re: Specifying Conditions for Subjects with Multiple Rows

Posted in reply to daszlosek

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,977

Re: Specifying Conditions for Subjects with Multiple Rows

Posted in reply to daszlosek

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.

Solution
‎03-18-2016 10:47 AM
Frequent Contributor
Posts: 76

Re: Specifying Conditions for Subjects with Multiple Rows

Posted in reply to daszlosek

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 204 views
  • 0 likes
  • 2 in conversation