select rows within different groups

[echoli]

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

[Reeza]

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;

[echoli]

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

[PGStats]

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?

[echoli]

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

[PGStats · Accepted solution]

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;

[echoli]

Hi PG,

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

Chen