topic Re: filtering out records in SAS Programming

filtering out records

Ranjeeta — Wed, 11 Mar 2020 17:52:26 GMT

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

Re: filtering out records

jimbarbour — Wed, 11 Mar 2020 18:10:23 GMT

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

Re: filtering out records

ed_sas_member — Wed, 11 Mar 2020 18:06:59 GMT

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;

Re: filtering out records

Ranjeeta — Wed, 11 Mar 2020 18:13:30 GMT

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

Re: filtering out records

ed_sas_member — Wed, 11 Mar 2020 18:33:16 GMT

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;

Re: filtering out records

Reeza — Wed, 11 Mar 2020 19:19:31 GMT

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

Re: filtering out records

Ranjeeta — Wed, 11 Mar 2020 19:36:53 GMT

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;

Re: filtering out records

Ranjeeta — Wed, 11 Mar 2020 19:37:44 GMT

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