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 ADJ
1234 ADJ
1234 ORIG
1234 ORIG
5556 ADJ
5556 ADJ
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
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;
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;
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;
I was having a similar problem and your solution worked for me. Thank you.
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 ;
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
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
Hi.
Peter.
Maye I or You misunderstood what op's mean.
Not sure Op want only one provider or muli-providers.
Ksharp
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
1234ADJ
1234ADJ
1234ORIG
1234ORIG
5556ADJ
5556ADJ
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;
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;
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.