topic How to flag a variable based on a condition in SAS Programming

How to flag a variable based on a condition

SAS_USER_928 — Mon, 21 Feb 2022 23:07:14 GMT

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.

Re: How to flag a variable based on a condition

Tom — Mon, 21 Feb 2022 23:26:16 GMT

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;

Re: How to flag a variable based on a condition

seemiyah — Tue, 22 Feb 2022 04:10:29 GMT

....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;

Re: How to flag a variable based on a condition

SAS_USER_928 — Tue, 22 Feb 2022 20:43:12 GMT

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.