topic select only specific group in SAS Programming

select only specific group

Karo_22 — Wed, 21 Sep 2011 14:09:09 GMT

I want to select those providers that have combination of rec_id not just one (adj or orig)…need to have both types of Rec ID.

Here is example of my data

PROVIDER REC_ID

1234 ADJ

1234 ORIG

5556 ADJ

Any ideas on how I can get that? From above example I simply would just have 1234 provider in my final data set and I would drop 5556 provider since he has only ADJ type of REC_ID

select only specific group

art297 — Wed, 21 Sep 2011 14:22:25 GMT

You could do it fairly easily with proc sql. e.g.,

proc sql;

select distinct provider

from have

group by provider

having min(rec_id) ne max(rec_id)

;

quit;

select only specific group

art297 — Wed, 21 Sep 2011 14:33:36 GMT

Or, since you mentioned "final data set", if you want all of the records for cases that meet the criteria you could use something like:

proc sql noprint;

create table want as

select *

from have

group by provider

having min(rec_id) ne max(rec_id)

;

quit;

select only specific group

Howles — Wed, 21 Sep 2011 21:18:03 GMT

This is also a good place to utilize the use of DISTINCT with summary functions:

having count(distinct rec_id) GT 1

art297 wrote:

Or, since you mentioned "final data set", if you want all of the records for cases that meet the criteria you could use something like:

proc sql noprint;

create table want as

select *

from have

group by provider

having min(rec_id) ne max(rec_id)

;

quit;

select only specific group

Peter_C — Thu, 22 Sep 2011 12:57:49 GMT

I think a simple data step is enough for this data

data result ;

call missing( adj_ct, ori_ct ) ;

do until( last.provider ) ;

set your.data ;

by provider ;

adj_ct +( rec_id='ADJ' ) ;

ori_ct +( rec_id='ORIG' ) ;

end ;

if adj_ct and ori_ct ;

drop adj_ct ori_ct ;

run ;

select only specific group

Ksharp — Fri, 23 Sep 2011 03:33:34 GMT

But .Peter

I tested your code. It is not right.

if adj_ct and ori_ct ; only can hold the last.provider.

See the following:

data temp;
input PROVIDER     $                      REC_ID $;
cards;
1234                                       ADJ
1234                                       ADJ
1234                                       ADJ
1234                                       ORIG
1234                                       ORIG
5556                                       ADJ
5556                                       ADJ
5556                                       ADJ
;
run;
data result ;
call missing( adj_ct, ori_ct ) ;
do until( last.provider ) ;
set temp ;
by provider ;
adj_ct +( rec_id='ADJ' ) ;
ori_ct +( rec_id='ORIG' ) ;
end ;
if adj_ct and ori_ct ;
drop adj_ct ori_ct ;
run ;

Ksharp

select only specific group

Peter_C — Fri, 23 Sep 2011 15:42:47 GMT

Ksharp

I thought one row was the required answer : indicating the provider with both (distinct) REC_ID

For that purpose my code seems appropriate:

adding another provider with rows like

789 ORIG

789 ADJ

my data step creates two rows

I can offer a very simple variation if ALL rows for providers of both REC_ID, but the original posting was asking only about PROVIDERS

select only specific group

Ksharp — Mon, 26 Sep 2011 08:28:49 GMT

Hi.

Peter.

Maye I or You misunderstood what op's mean.

Not sure Op want only one provider or muli-providers.

Ksharp

select only specific group

CrowA — Mon, 26 Sep 2011 21:00:41 GMT

I was having a similar problem and your solution worked for me. Thank you.

select only specific group

kassimorra — Mon, 26 Sep 2011 22:10:46 GMT

Hi Karo,

I made an exemple that make what you need. The same example can be build in SQL sintax.

Please let me know if it works !

Regards.

Kassim

/******

* START THE CODE HERE !!!!

*******/

/* CREATE A TABLE TO TEST */

data teste;

input PROVIDER 4. REC_ID $3.;

datalines;

1234ADJ

1234ORIG

5556ADJ

;

run;

/* SORT IT BY THE KEY FIELDS */

proc sort data=teste;

by provider rec_id;

run;

/* COUNT HOW MANY KEYS HAS THE KEY FIELD */

data teste1;

set teste;

by provider rec_id;

retain cont type;

if first.provider then do;

type = "";

cont = 0;

end;

if first.rec_id then do;

if type = "" or type ne rec_id then do;

type = rec_id;

cont = sum(cont, 1);

end;

run;

/* MAKE A JOIN TO RETAIN THE IMPORTANT DATA */

proc sql;

create table teste2 as

select tb0.*

from teste tb0

inner join (select distinct * from teste1 where cont gt 1) tb1

on tb0.provider = tb1.provider

;

quit;