selecting records, ranked variables

[Batta]

I apologize that I could not find better way to define my subject line.

This is the example of the data set: I have subjects , with different visit number (visit_num) 1-3 with treatment, "yes" for treatment received and "no" for treatment not received. I need to select/list subjects that look like subject s2, that has the last visit (3) with treatment="yes" but the previous visit (2) with treatment = "no" (others have last visit treatment "no" while the previous one was "yes". Your help is highly appreciated. Thanks a lot.

subject	visit_num	treatment
s1	1	yes
s1	2	yes
s1	3	no
s2	1	yes
s2	2	no
s2	3	yes
s3	1	yes
s3	2	yes
s3	3	no

[novinosrin]

Hi @Batta  Do all subjects have the same 1-3 vistnum?

If Yes-->

data have;
input subject $	visit_num	treatment $;
cards;
s1	1	yes
s1	2	yes
s1	3	no
s2	1	yes
s2	2	no
s2	3	yes
s3	1	yes
s3	2	yes
s3	3	no
;

proc sql;
 create table want as
 select *
 from have
 group by subject
 having max(visit_num=2 and treatment='no') and max(visit_num=3 and treatment='yes')
 order by subject,visit_num;
quit;

[mklangley]

Is the only condition that last visit has to be "yes" and the previous visit is "no"? And will there always be only three visits per subject?

If so, as your example indicated, here's one way to do it--by transposing your original dataset:

data have;
    input subject $ visit_num treatment $;
    datalines;
s1 1 yes
s1 2 yes
s1 3 no
s2 1 yes
s2 2 no
s2 3 yes
s3 1 yes
s3 2 yes
s3 3 no
    ;
run;

proc transpose data=have out=have_transposed prefix=visit_num;
    by subject;
    id visit_num;
    var treatment;
run;

proc sql;
    create table want as
    select distinct subject
    from have_transposed
    where visit_num3 = "yes" 
        and visit_num2 = "no";
quit;

[Batta]

Thanks, 

I am sorry, I made a mistake, so the data came not quite completed:

Number of visits can vary, some subject may have 5, some other subject can have 10, the point is that I always select the LAST visit and the visit before the last one and those subject that always have the last visit treatment "yes" and the previous one was "no"

My apology for not properly explaining the data.

THank you very much.

[novinosrin]

data have;
input subject $	visit_num	treatment $;
cards;
s1	1	yes
s1	2	yes
s1	3	no
s2	1	yes
s2	2	no
s2	3	yes
s3	1	yes
s3	2	yes
s3	3	no
;

data want;
 do _n_=1 by 1 until(last.subject);
  set have;
  by subject;
  length k $3;
  if not last.subject then  k=treatment;
 end;
 _iorc_=k='no' and treatment='yes';
 do _n_=1 to _n_;
  set have;
  if _iorc_ then output;
 end;
 drop k;
run;

[Batta]

Thank you very much! 

This works great! 

If it's not big problem to do: is it possible to drop all other visits from the output but the last one and the one before the last one, since all other before are not relevant to have them in the output.

I really appreciate your dedicated time and your wish to help.

[novinosrin]

HI @Batta  Do you mean this?

data want;
 do _n_=1 by 1 until(last.subject);
  set have;
  by subject;
  length k $3;
  if not last.subject then  k=treatment;
 end;
 _iorc_=k='no' and treatment='yes';
 k1=_n_;
 do _n_=1 to _n_;
  set have;
  if _iorc_ and _n_ >=k1-1  then output;
 end;
 drop k:;
run;

[Batta · Accepted solution]

That's it! 

Thank you very much Sir!.

I appreciate your effort and your time dedicated to this!

Kind regards!

Batta

[Batta]

Ah, mistakenly I click on my own reply to accept solution. I hope that admins can correct this...

[PeterClemmensen]

You can easily correct this yourself, no Admin needed 🙂