topic Re: how to delete a record depending on specific variable in New SAS User

how to delete a record depending on specific variable

hjjijkkl — Wed, 14 Jul 2021 18:57:32 GMT

I have a big data and I want to keep records where the term variable is either 0 or missing for that specific ID. For example in the table below, ID 2 has term=1 so, I want to delete the whole record for ID 2 from the data. How can I do that?

D	term
1	0
1
2	1
2
2
3	.
3

This is the outcome I want to have

D	term
1	0
1
3	.
3

Re: how to delete a record depending on specific variable

PaigeMiller — Wed, 14 Jul 2021 19:08:22 GMT

@hjjijkkl wrote:

I have a big data and I want to keep records where the term variable is either 0 or missing for that specific ID. For example in the table below, ID 2 has term=1 so, I want to delete the whole record for ID 2 from the data. How can I do that?

So, I think your explanation needs clarification. In the first sentence, you want to keep records where term is either 0 or missing. Does that mean you want to keep just those records are either 0 or missing; or does that mean you want to keep the entire ID if you get a 0 or missing? Or does that mean something else?

In your example where ID 2 has term=1, you want to delete it. What if there are mixed 0s and missings and 1s for term for an ID?

Re: how to delete a record depending on specific variable

Reeza — Wed, 14 Jul 2021 19:13:23 GMT

@hjjijkkl wrote:

I have a big data and I want to keep records where the term variable is either 0 or missing for that specific ID. For example in the table below, ID 2 has term=1 so, I want to delete the whole record for ID 2 from the data. How can I do that?

D

term

1

0

1

2

1

2

2

3

.

3

This is the outcome I want to have

D

term

1

0

1

3

.

3

proc sql;
create table want as
select * from have
where D NOT IN (select distinct D from have where term=1);
quit;

Re: how to delete a record depending on specific variable

novinosrin — Wed, 14 Jul 2021 19:30:38 GMT

Hi @hjjijkkl I just love these fun questions-


data have ;
  infile cards truncover ;
  input ID $	term ;
cards ;
1	0
1	
2	1
2	
2	
3	.
3	
;

proc sql;
 create table want as
 select *
 from have
 group by id
 having not max(term) ;
quit;

proc print noobs ; run ;

ID	term
1	0
1	.
3	.
3	.

Re: how to delete a record depending on specific variable

Reeza — Wed, 14 Jul 2021 19:36:25 GMT

Breaks if you have no 1 in the data so not a really robust solution but meets minimal requirements 😉