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
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
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;
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;
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;
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
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: