Hi,
I have the following scenario and am stuck with creating the output.
A patient is required to perform one of two labs per visit. Therefore, I have 2 observations per patient per visit, one as DONE and other as NOT DONE, in the input data set. I'm trying to flag patients' lab status per visit based on whether the lab was performed or not. If only one lab was DONE (at any visit) then flag it as DONE, else NOT DONE. Therefore, The output data set will have only 1 observation per patient per visit flagged either as DONE or NOT DONE depending on the above criteria.
E.g., below is the input data set with 2 patients:
PatientID | VisitID | Date | LabStatus |
A | 1 | 1/1/2022 | Done |
A | 1 | 1/1/2022 | Not Done |
A | 2 | 1/5/2022 | Done |
A | 2 | 1/5/2022 | Not Done |
B | 1 | 1/15/2022 | Done |
B | 1 | 1/15/2022 | Not Done |
B | 2 | 1/20/2022 | Not Done |
B | 2 | 1/20/2022 | Not Done |
OUTPUT Data Set:
PatientID | VisitID | Flag |
A | 1 | Done |
A | 2 | Done |
B | 1 | Done |
B | 2 | Not Done |
Thank you all for the help.
Since D comes before N use the MIN() aggregate function.
proc sql;
create table WANT as
select paitentid,visitid,min(labstatus) as Flag
from have
group by patientid,visitid
;
quit;
Since D comes before N use the MIN() aggregate function.
proc sql;
create table WANT as
select paitentid,visitid,min(labstatus) as Flag
from have
group by patientid,visitid
;
quit;
....and if we weren't lucky enough to have the status in the correct alphabetical order, something like the following would also work.
proc sql;
create table want as
select patientid
,visitid
,ifc(sum(labstatus='Done'),'Done','Not Done') as Flag
from have
group by patientid
,visitid
;
quit;
Thank you so much both of you for your prompt responses. Actually both solutions worked for me and wanted to mark both as accepted solutions but the platform did not let me do so. Again, thank you so so much.
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.