topic Re: find subjects with more than 3 duplicate values in SAS Programming

find subjects with more than 3 duplicate values

ari — Wed, 03 Aug 2016 14:28:50 GMT

Have:

id	var1	var2
1	5000	1
2	2015	1
2	2015	1
3	700	1
3	700	1
3	700	1

2	2015	1
3	700	1

want:

id	var1	var2
3	700	1
3	700	1
3	700	1

I think there should be an easy way to this in SAS. I need to select the id's where 3 or more observations of the same ID have var2=1.

Any ideas?

Thanks

Re: find subjects with more than 3 duplicate values

Kurt_Bremser — Wed, 03 Aug 2016 13:36:46 GMT

proc sql;
create table want as
select id from have
where var2 = 1
group by id
having count(*) >= 3
;
quit;

Re: find subjects with more than 3 duplicate values

ari — Wed, 03 Aug 2016 13:51:22 GMT

Re: find subjects with more than 3 duplicate values

Kurt_Bremser — Wed, 03 Aug 2016 13:49:52 GMT

Just add the other variables in the select list of the SQL statement:

data have;
input id var1 var2;
cards;
1 5000 1
2 2015 1
2 2015 1
3 700 1
3 700 1
3 700 1
;
run;

proc sql;
create table want as
select id, var1, var2 from have
where var2 = 1
group by id
having count(*) >= 3
;
quit;

You can also see the best way to provide test data (data step with inline data in cards;)

Re: find subjects with more than 3 duplicate values

ari — Wed, 03 Aug 2016 14:33:18 GMT

Hi Kurt,

this program does not preserve the order of the id's. I need to look at the subsequent observation of the same id's.

for instance,

cards;1 5000 1
2 2015 1
2 2015 1
3 700 1
3 700 1
3 700 1
2 2015 1
3 700 1
3 700 1;
run;

I want to select those id's separately rather than combining them together.

Re: find subjects with more than 3 duplicate values

Astounding — Wed, 03 Aug 2016 14:59:24 GMT

ari,

Kurt's program still gets you most of the way there. If you want to extract all records for those IDs, use Kurt's results to extract:

proc sql;

create table want_list as

select id from have

where var2=1

group by id

having count(*) >= 3;

create table want as

select * from have where id in (select id from want_list);

quit;

You may need to correct the syntax on the final SELECT ... I'm much more at home with a DATA step than SQL.

Even in this sample code, SQL does not promise to maintain the order of the incoming records. It's just a characteristic of SQL. If you want to guarantee that order, you will need to use a DATA step instead.

Re: find subjects with more than 3 duplicate values

Kurt_Bremser — Thu, 04 Aug 2016 05:21:18 GMT

Do you only need to preserve the original order, or do you need to select ID's that have three successive 1's in the original dataset?

Stating your complete requirements shortens the process of finding a working solution considerably 😉

Re: find subjects with more than 3 duplicate values

LinusH — Thu, 04 Aug 2016 07:07:48 GMT

If the original sort order is that important you need a variable that holds that information. Like a datatime. If that's doesn't exist have a sequence no added in a preceeding step, then use that in an order by clause in @Kurt_Bremser SQL.

Re: find subjects with more than 3 duplicate values

ari — Thu, 04 Aug 2016 09:37:17 GMT

Hi Kurt,

Yes, I need to preserve the original order and at the same time I need to select ID's with 3 successive 1's. I think sequence no as suggested by Linush works. I will try that option.

Thanks,

Re: find subjects with more than 3 duplicate values

ari — Thu, 04 Aug 2016 09:37:41 GMT

Sounds good idea. I will check this. Thanks

Re: find subjects with more than 3 duplicate values

Ksharp — Thu, 04 Aug 2016 10:38:48 GMT


data have;
input id var1 var2;
cards;
1 5000 1
2 2015 1
2 2015 1
3 700 1
3 700 1
3 700 1
;
run;
data want;
 do i=1 by 1 until(last.id);
  set have;
  by id;
 end;
 do until(last.id);
  set have;
  by id;
  if i gt 2 then output;
 end;
 drop i;
run;