BookmarkSubscribeRSS Feed
ari
Quartz | Level 8 ari
Quartz | Level 8

Have:

 

idvar1var2
150001
220151
220151
37001
37001
3700

1

220151
3700

1

 

want:

idvar1var2
37001
37001
37001

 

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

10 REPLIES 10
Kurt_Bremser
Super User

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;)

ari
Quartz | Level 8 ari
Quartz | Level 8

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.

 

Astounding
PROC Star

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.

Kurt_Bremser
Super User

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 😉

ari
Quartz | Level 8 ari
Quartz | Level 8

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,

 

LinusH
Tourmaline | Level 20
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.
Data never sleeps
ari
Quartz | Level 8 ari
Quartz | Level 8

Sounds good idea. I will check this. Thanks

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1183 views
  • 1 like
  • 5 in conversation