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
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;
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;
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
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?
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
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;
Hi PG,
That works!!! thanks so much for your help!!!
Chen
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.