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
proc sql;
create table want as
select id from have
where var2 = 1
group by id
having count(*) >= 3
;
quit;
a
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;)
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.
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.
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 😉
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,
Sounds good idea. I will check this. Thanks
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;
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.