topic if values stored in different variables are not equal then output in SAS Programming

if values stored in different variables are not equal then output

Anita_n — Wed, 12 Jul 2023 07:35:51 GMT

Dear all,

if I have a data that looks like this:

data have;
	infile datalines;
	input Treatment $10. Visit1 $10. Visit2 $14. Visit3 $15.;
	datalines;
chemo	  completed	completed     completed
Immune	  ongoing	ongoing	      ongoing
Stem cell cancelled	cancelled     cancelled
chemo	  ongoing	completed     cancelled
Targeted  ongoing	starting soon ongoing
Brachy	  cancelled	cancelled	  cancelled
surgery	  cancelled	ongoing	      starting soon
chemo	  completed	starting soon completed
run;

I want to look through the variable treatment and any time I find chemo I will like to output all values of subsequent variables. If they are equal then this should be outputted once but if not equal the I will like to output all values. Here is my start code:

data want;
set have;
if find(treatment, "chemo")>0 then do;
if visit1=visit2 and visit2=visit3 then status=visit1;
end;
run;

for example in the first observation I find chemo, so I will check if the values of visit1-visit3 are equal. In this case yes, so the value completed is outputted once.

In the fourth observation I find chemo but the values of visit1-visit3 are not equal so I will like to output status=ongoing,completed and cancelled

Any help?

Re: if values stored in different variables are not equal then output

Kurt_Bremser — Wed, 12 Jul 2023 07:42:35 GMT

proc transpose
  data=have (where=(find(treatment,"chemo") > 0))
  out=want (drop=_name_ rename=(col1=visit))
;
by treatment;
var visit:;
run;

proc sort data=want nodupkey;
by treatment visit;
run;

Re: if values stored in different variables are not equal then output

Anita_n — Wed, 12 Jul 2023 08:00:38 GMT

@Kurt_Bremser : thanks for the quick reply but that is not what I really want. The result should look somehow like this:

data want;
	infile datalines;
	input Treatment $5. status $15.;
	datalines;
chemo completed
chemo ongoing
chemo completed
chemo cancelled
chemo completed
chemo starting soon
chemo compledted
;
run;

Thanks

Re: if values stored in different variables are not equal then output

Patrick — Wed, 12 Jul 2023 11:03:30 GMT

Like that?

data have;
  infile datalines dsd dlm=',';
  input Treatment :$10. Visit1 :$15. Visit2 :$15. Visit3 :$15.;
  datalines;
chemo,completed,completed,completed
chemo,ongoing,completed,cancelled
chemo,completed,starting soon,completed
;

data want;
  set have;
  length Visit $15;
  if treatment='chemo';
  visit=visit1;
  output;
  if visit ne visit2 then
    do;
      visit=visit2;
      output;
    end;
  if visit ne visit3 then
    do;
      visit=visit3;
      output;
    end;
  keep Treatment Visit;
run;
proc print data=want;
run;

Re: if values stored in different variables are not equal then output

Ksharp — Wed, 12 Jul 2023 11:37:57 GMT

/*
Assuming I understood what you mean.
*/
data have;
 infile datalines;
 input Treatment $10. Visit1 $10. Visit2 $14. Visit3 $15.;
 datalines;
chemo   completed completed     completed
Immune   ongoing ongoing       ongoing
Stem cell cancelled cancelled     cancelled
chemo   ongoing completed     cancelled
Targeted  ongoing starting soon ongoing
Brachy   cancelled cancelled   cancelled
surgery   cancelled ongoing       starting soon
chemo   completed starting soon completed
;
run;

data want;
 if _n_=1 then do;
  length k status $ 200;
  call missing(k);
  declare hash h();
  h.definekey('k');
  h.definedone();
 end;
set have(where=(Treatment='chemo'));
array x{*} $ Visit1-Visit3;
h.clear();
do i=1 to dim(x);
  if not missing(x{i}) then do;k=x{i};h.ref();end;
end;
if h.num_items=1 then do;status=coalescec(of x{*});output;end;
if h.num_items>1 then do;
 do i=1 to dim(x);
   status=x{i};
   if not missing(status) then output;
 end;
end;
keep Treatment status;
run;

Re: if values stored in different variables are not equal then output

Tom — Wed, 12 Jul 2023 13:51:51 GMT

So you just want to output the DISTINCT values of the multiple VISIT variables?

Why not just transpose the data so there is only ONE variable to hold the VISIT information?

Then you can just use PROC SORT with NODUPKEY to reduce it to just the set of distinct values.