filtering out records

[Ranjeeta]

data discard keep;
set input;
by id;
if service in (4,13,20) then output discard;
if service in (4,5,6,7,8,13,20) then output keep;
run;

Hello

Can someone advise if the code above is correct to filter out the following records

If a patient has service equal to only 4 or 13, or 20 the exclude those patients

but if a patient has service equal to 5 or 6 or 7 or 8 along with 4 or 13, or 20 then keep them 

Many thanks 

[jimbarbour]

Ranjeeta,

I would re-code as follows otherwise you will have cases with the same patient occurring in both output datasets:

data discard keep;
set input;
by id;
if  service in (4,13,20) then 
    output discard;
else
    output keep;
run;

 Jim

[ed_sas_member]

Hi @Ranjeeta 

I would recommend that you use another name than "keep" for your output dataset as it may be confusing (SAS word).

There is an inconsistency in your code between both IF statements:

-> a patient who will have service=4 or 13 or 20 will be kept in both tables.

In your input dataset, are there many rows by patient?

data discard keep;
set input;
by id;
if service in (4,13,20) then output discard;
if service in (4,5,6,7,8,13,20) then output keep;
run;

[Ranjeeta]

There are many rows per patient
what im trying to get is the patients who received services 4, or 13, or 20 along with 5 or 6 or 7 or 8
but if they received only 4, or 13, or 20 then dont keep them

[ed_sas_member]

Hi @Ranjeeta 

Could you please test if the following code meet your expectations?

Best,

/*Retrieve the list of id for each table*/
proc transpose data=input out=input_tr (drop=_:) prefix=service;
	var service;
	by id;
run;

data id_tobediscarded id_tobekept;
	set input_tr;
	by id;
	length service_all $ 20;
	service_all = catx(",",of service:);
	if prxmatch('/(4|13|20)/',service_all) and prxmatch('/(5|6|7|8)/',service_all) then output id_tobekept;
	else if prxmatch('/(4|13|20)/',service_all) then output id_tobediscarded;
	drop service:;
run;

proc sql;
/*First table: keep*/
	create table keep as
	select a.*
	from input as a inner join id_tobekept as b
	on a.id = b.id;
/*Second table: discard*/
	create table discard as
	select a.*
	from input as a inner join id_tobediscarded as b
	on a.id = b.id;
quit;

[Ranjeeta]

Thankyou was just trying the code but the 2nd step is not returning any obs 

data id_tobediscarded id_tobekept;
	set input_tr;
	by id;
	length service_all $ 20;
	service_all = catx(",",of service:);
	if prxmatch('/(4|13|20)/',service_all) and prxmatch('/(5|6|7|8)/',service_all) then output id_tobekept;
	else if prxmatch('/(4|13|20)/',service_all) then output id_tobediscarded;
	drop service:;
run;

[Reeza]

Are there other codes that may have to be ignored besides those values?

---

@Ranjeeta wrote:
There are many rows per patient
what im trying to get is the patients who received services 4, or 13, or 20 along with 5 or 6 or 7 or 8
but if they received only 4, or 13, or 20 then dont keep them

---

[Ranjeeta]

No there are no other service codes that need to be ignored