DATA Step, Macro, Functions and more

select rows within different groups

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

select rows within different groups

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


Accepted Solutions
Solution
‎03-10-2017 09:55 AM
Respected Advisor
Posts: 4,919

Re: select rows within different groups

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;
PG

View solution in original post


All Replies
Super User
Posts: 19,768

Re: select rows within different groups

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;
Frequent Contributor
Posts: 84

Re: select rows within different groups

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

Respected Advisor
Posts: 4,919

Re: select rows within different groups

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?

PG
Frequent Contributor
Posts: 84

Re: select rows within different groups

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

Solution
‎03-10-2017 09:55 AM
Respected Advisor
Posts: 4,919

Re: select rows within different groups

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;
PG
Frequent Contributor
Posts: 84

Re: select rows within different groups

Hi PG,

 

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

 

Chen

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 203 views
  • 1 like
  • 3 in conversation