topic Re: selecting records, ranked variables in SAS Programming

selecting records, ranked variables

Batta — Wed, 13 Jan 2021 20:40:54 GMT

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

Re: selecting records, ranked variables

novinosrin — Wed, 13 Jan 2021 20:53:32 GMT

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;

Re: selecting records, ranked variables

mklangley — Wed, 13 Jan 2021 20:57:10 GMT

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;

Re: selecting records, ranked variables

novinosrin — Wed, 13 Jan 2021 21:11:38 GMT


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;

Re: selecting records, ranked variables

Batta — Wed, 13 Jan 2021 21:26:59 GMT

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.

Re: selecting records, ranked variables

Batta — Wed, 13 Jan 2021 21:42:11 GMT

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.

Re: selecting records, ranked variables

novinosrin — Wed, 13 Jan 2021 21:49:03 GMT

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;

Re: selecting records, ranked variables

Batta — Thu, 14 Jan 2021 01:55:00 GMT

That's it!

Thank you very much Sir!.

I appreciate your effort and your time dedicated to this!

Kind regards!

Batta

Re: selecting records, ranked variables

Batta — Thu, 14 Jan 2021 01:58:08 GMT

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

Re: selecting records, ranked variables

PeterClemmensen — Thu, 14 Jan 2021 08:27:15 GMT

You can easily correct this yourself, no Admin needed 🙂