topic Re: How to check if the value of a variable is same in a group in SAS Programming

How to check if the value of a variable is same in a group

abhisas1 — Mon, 15 Aug 2022 21:01:59 GMT

Hello all ,

I'm working on a dataset where i have to check if value(multiple records) of variable is same for the subject and create a new variable if all the values of the variable for a subject are same.

EX: a subject is given treatments every week. i have to check if the sujbect was given Treatment A every week , then new_variable = "TREATMENT A".

data chk;

input ID week$ treatment$;

datalines;

1 week1 TreatmentA

1 week2 TreatmentA

1 week3 TreatmentA

2 week1 TreatmentB

2 week2 TreatmentB

2 week3 TreatmentA

3 week1 TreatmentB

3 week2 TreatmentB

3 week3 TreatmentB

;

RUN;

question:

If treatment is same every week then assign that treatment to new_variable . Could you please help me with this.

Thank you

Re: How to check if the value of a variable is same in a group

Patrick — Tue, 16 Aug 2022 01:36:31 GMT

Below one option how to do this.

data have;
  infile datalines truncover;
  input ID week $ treatment $20.;
  datalines;
1 week1 TreatmentA 
1 week2 TreatmentA
1 week3 TreatmentA
2 week1 TreatmentB
2 week2 TreatmentB
2 week3 TreatmentA
3 week1 TreatmentB
3 week2 TreatmentB
3 week3 TreatmentB
;

proc sql;
  create table want as
  select 
    *,
    case
      when max(treatment)=min(treatment) then treatment
      else ' '
      end as new_var
  from have
  group by id
  order by id,week
  ;
quit;

Re: How to check if the value of a variable is same in a group

Kurt_Bremser — Tue, 16 Aug 2022 07:55:43 GMT

Reading a variable with $ without specifying a length defines it with the default length of 8, which is not sufficient for your data.

data chk;
input ID week$ treatment :$10.;
datalines;
1 week1 TreatmentA 
1 week2 TreatmentA
1 week3 TreatmentA
2 week1 TreatmentB
2 week2 TreatmentB
2 week3 TreatmentA
3 week1 TreatmentB
3 week2 TreatmentB
3 week3 TreatmentB
;

From this, a double DO loop can do it, as your data seems to already be sorted by id:

data want;
do until (last.id);
  set chk;
  by id;
  if first.id
  then newvar = treatment;
  if newvar ne treatment then newvar = "";
end;
do until (last.id);
  set chk;
  by id;
  output;
end;
run;

Re: How to check if the value of a variable is same in a group

Ksharp — Tue, 16 Aug 2022 12:34:33 GMT

data have;
  infile datalines truncover;
  input ID week $ treatment $20.;
  datalines;
1 week1 TreatmentA 
1 week2 TreatmentA
1 week3 TreatmentA
2 week1 TreatmentB
2 week2 TreatmentB
2 week3 TreatmentA
3 week1 TreatmentB
3 week2 TreatmentB
3 week3 TreatmentB
;

proc sql;
  create table want as
  select 
    *,
    case
      when count(distinct treatment)=1 then treatment
      else ' '
      end as new_var
  from have
  group by id
  order by id,week
  ;
quit;

Re: How to check if the value of a variable is same in a group

abhisas1 — Tue, 16 Aug 2022 14:18:42 GMT

Thank you

Re: How to check if the value of a variable is same in a group

abhisas1 — Tue, 16 Aug 2022 14:20:05 GMT

Thank you ! I was looking forward to learn how to do it with DO untill and now i have the answer.

Re: How to check if the value of a variable is same in a group

abhisas1 — Tue, 16 Aug 2022 14:21:03 GMT

Thank you for your quick response. Used this code and worked perfectly