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

[abhisas1]

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

[Patrick]

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;

[abhisas1]

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

[Kurt_Bremser]

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;

[abhisas1]

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

[Ksharp · Accepted solution]

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;

[abhisas1]

Thank you