topic Re: In repeated measure how do i delete unwanted ids in SAS Procedures

In repeated measure how do i delete unwanted ids

Douce — Wed, 20 Apr 2016 21:19:28 GMT

here is the the structure of my data below:

i only need ID 2 since all patients are alive, therefore trying to delete id1:

Id sex status

1 2 A

1 2 D

2 1 A

Re: In repeated measure how do i delete unwanted ids

Astounding — Wed, 20 Apr 2016 21:01:51 GMT

The simplest program might be this. Assuming your data set is already sorted by ID:

data want;

merge have (where=(status='D') in=delete_me) have;

by ID;

if delete_me then delete;

run;

Note that this deletes IDs that contain a "D" only. If you want to delete anything but "A" you would replace the WHERE condition:

where=(status ne 'A')

While simplest, this approach will unfortunately give you some undesirable messages in the log. The messages won't hurt in this particular case, but could under other circumstances. So a more complex, cleaner method would be:

data want;

wanted='Y';;

do until (last.id);

set have;

by ID;

if status='D' then wanted='N';

end;

do until (last.id);

set have;

by ID;

if wanted='Y' then output;

end;

run;

As before, you could conceivably change the conditions:

if status ne 'A' then wanted='N';

Re: In repeated measure how do i delete unwanted ids

Douce — Wed, 20 Apr 2016 21:09:10 GMT

I appreciate your prompt response.

I was using the first method and in fact if was only omiting obs where id='d'; when my goal is to remove all obs with that id; ie delete id 1 all together and ending with id 2 only.

Re: In repeated measure how do i delete unwanted ids

Pamela_JSRCC — Wed, 20 Apr 2016 21:09:11 GMT

I usually create a "discard" table before deleting from the data. I'm assuming that 'A' is for Alive and 'D' is for Dead, but what is 'R'? Anyway, the discard table below is populated by any rows where the subject is not 'A', so we can delete any ID from the original table that appears in the discard table.

proc sql;
create table discard as
select *
from tbl
where status ^= 'A'
;
delete from tbl
where id in (select id from discard);

Re: In repeated measure how do i delete unwanted ids

Douce — Wed, 20 Apr 2016 21:15:53 GMT

R is for retransplant 🙂 thank you for your suggestion.

Re: In repeated measure how do i delete unwanted ids

Ksharp — Thu, 21 Apr 2016 02:16:13 GMT

data have;
input Id  sex  status $;
cards;
1    2     A
1    2     A
1    2     A
1    2     D
2    1     A
2    1     A
2    1     A
;
run;

proc sql;
 create table want as
  select * from have
   group by id
    having sum(status='A')=count(*);
quit;

Re: In repeated measure how do i delete unwanted ids

Babloo — Thu, 21 Apr 2016 11:37:15 GMT

Could you please tell me how the following 'having' clause will work in below proc sql? what variable it will sum?

proc sql;
 create table want as
  select * from have
   group by id
    having sum(status='A')=count(*);
quit;

Re: In repeated measure how do i delete unwanted ids

FreelanceReinh — Thu, 21 Apr 2016 14:12:43 GMT

Hi @Babloo,

This SUM function adds Boolean values, more precisely: their numeric equivalents 1 (for TRUE) and 0 (for FALSE). These arise from the equation status='A'. So, an observation with STATUS='A' contributes a 1 to the sum and other observations a 0. Thus, sum(status='A') is the number of observations with STATUS='A' in the BY group (due to the GROUP BY clause). If this number is equal to count(*), the number of all observations in the BY group, this means that all observations in the BY group must have STATUS='A'.

Re: In repeated measure how do i delete unwanted ids

Pamela_JSRCC — Thu, 21 Apr 2016 14:18:29 GMT

It will sum the values from the expression "status='A'" by ID and compare that to the number of rows, so it will keep the ones where all the rows with that ID have status='A'. This is nifty usage of the group by and having clause. To see what is going on, like when debugging, you could "name" the flag and view an "intermediate" table:

data tbl;

input id sex status $;

datalines;

1 2 A

1 2 R

1 2 A

1 2 D

2 1 A

;

/* Create intermediate table to see the flag */

proc sql;

create table have as

select *, status='A' as alive_flag 'alive flag'

from tbl

;

select * from have

;

create table want as

select * from have

group by id

having sum(alive_flag)=count(*)

;

select * from want;

alive

id sex status flag

-------------------

1 2 A 1

1 2 R 0

1 2 A 1

1 2 D 0

2 1 A 1

alive

id sex status flag

-------------------

2 1 A 1