topic Re: select rows within different groups in SAS Programming

select rows within different groups

echoli — Tue, 07 Mar 2017 23:13:27 GMT

Hi all,

I have a problem about how select rows of what I want:

for example, I have several subjects with several variables:

group id timepoint

TX 01 1

TX 01 2

TX 02 1

TX 03 1

TX 04 2

CT 05 1

CT 06 2

I want to select id(s) in TX group but have timepoint 1 and 2 simultaneously. That means I want to delete rows with highlight green color. Anyone has a good idea if I want to use proc sql? Or other ways are okay.

Many thanks,

Chen

Re: select rows within different groups

Reeza — Tue, 07 Mar 2017 23:40:35 GMT

Is it only 1 & 2 or could you have duplicates or 1/2/3?

If it's only 1 & 2 then this works:

data want;
set have;

by group id; *may need to sort your data first;

if not (first.id and last.id); *if unique then a record is first and last at same time;

run;

Re: select rows within different groups

PGStats — Wed, 08 Mar 2017 04:24:56 GMT

It is not clear to me why you keep id=03 and id=04. Id=03 only has timepoint=1 and id=04 only has timepoint=2. Neither has timepoints 1 and 2 simultaneously. What am I missing?

Re: select rows within different groups

echoli — Wed, 08 Mar 2017 15:14:42 GMT

Hi PG,

Very sorry that I made mistakes in my example, the correct example should be:

group id timepoint

TX 01 1

TX 01 2

TX 02 1

TX 03 1

TX 03 2

CT 04 1

CT 04 2

CT 05 1

Highlight color in green are the rows not be selected.

Reeza has provide me a good way use data; set; But can i do this by proc sql?

Thanks so much for your reply!!!

Chen

Re: select rows within different groups

echoli — Wed, 08 Mar 2017 15:16:05 GMT

Hi Reeza,

Thanks so much for your help! It works!!

But can I have other ways, like proc sql, to solve this question?

Many thanks,

Chen

Re: select rows within different groups

PGStats — Thu, 09 Mar 2017 06:25:27 GMT

Sure :

data have;
input group :$2.   id    timepoint;
datalines;
TX       01     1
TX       01     2
TX       02     1
TX       03     1
TX       03     2
CT       04     1
CT       04     2
CT       05     1
;

proc sql;
create table want as
select *
from have
where group="TX"
group by group, id
having max(timepoint=1) and max(timepoint=2)
order by group, id, timepoint;
select * from want;
quit;

Re: select rows within different groups

echoli — Fri, 10 Mar 2017 14:55:35 GMT

Hi PG,

That works!!! thanks so much for your help!!!

Chen