BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Anita_n
Pyrite | Level 9

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?

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

Patrick_0-1689159804835.png

 

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User
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;
Anita_n
Pyrite | Level 9

@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

Patrick
Opal | Level 21

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;

Patrick_0-1689159804835.png

 

Ksharp
Super User
/*
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;
Tom
Super User Tom
Super User

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1643 views
  • 1 like
  • 5 in conversation