topic Re: Look at multiple observations to create a new variable in SAS Programming

Look at multiple observations to create a new variable

lauraem93 — Fri, 15 Jul 2022 03:47:08 GMT

I have a data set of patients and diagnosis codes, for example

name, code

Sarah, A1

Sarah, B1

Sarah, C1

Matt, A2

Matt, E2

Dave, A2

Dave, B3

Dave, E2

George, C2

George, A1

I am screening the patients for a research study. They all have A1 or A2. If they also have a B code or C code, they are not eligible for the study. I want to create a set like the following that just has 1 entry for each patient with a new variable that says if they are eligible and the code variable should be their A code.

name, code, eligible

Sarah, A1, no

Matt, A2, yes

Dave, A2, no

George, A1, no

How can I look at each code and then create one observation with a new variable to say if the patient is eligible?

Re: Look at multiple observations to create a new variable

andreas_lds — Fri, 15 Jul 2022 04:49:24 GMT

Assuming the data is grouped by name:

data want;
   set have;
   by name notsorted;

   length eligible 8 a_code $ 2;
   retain eligible a_code;

   if first.name then do;
      eligible = 1;
   end;

   if first(code) = 'A' then do;
      a_code = code;
   end;

   eligible = eligible and (first(code) not in ('B', 'C'));

   if last.name then do;
      code = a_code;
      output;
   end;
run;

Re: Look at multiple observations to create a new variable

Ksharp — Fri, 15 Jul 2022 13:08:38 GMT

data have;
infile cards dlm=' ,';
input name $ code $;
cards;
Sarah, A1
Sarah, B1
Sarah, C1
Matt, A2
Matt, E2
Dave, A2
Dave, B3
Dave, E2
George, C2
George, A1
;

proc sql;
create table want as
select name,min(code) as code,
ifc(sum(code eqt 'B' or code eqt 'C'),'no ','yes' ) as eligible
 from have 
  group by name;
quit;