topic Re: Combine the variable values vertically in New SAS User

Combine the variable values vertically

NewUsrStat — Sat, 16 May 2026 16:32:22 GMT

Hi guys,

suppose to have the following dataset:

data DB;
  input ID :$200. Reason1 :$200.Reason2 :$200.;
cards;
0001 Considered     Excluded
0001 Violated       Not_excluded
0001 Excluded       Not_Verified
0001 Excluded       Not_Verified   
run;

Is there a way to get the following?

data DB1;
  input ID :$200. Reason1 :$200.Reason2 :$200.;
cards;
0001 Considered,Violated,Excluded     Excluded,Not_excluded,Not_Verified
0001 Considered,Violated,Excluded     Excluded,Not_excluded,Not_Verified
0001 Considered,Violated,Excluded     Excluded,Not_excluded,Not_Verified   
run;

In other words I would like to combine into one row and by each variable "Reason*" all the values the variable(s) takes.

Note that, after the combination, I would like to repeat the combination the number of times the variable Reason* takes a value ID by ID.

In the real dataset I have Reason1 , Reason12, Reason3, ..., Reason10.

Can anyone help me please?

Re: Combine the variable values vertically

PaigeMiller — Sat, 16 May 2026 15:05:28 GMT

In my opinion, this is not a good idea, and it makes future programming difficult. See Maxim 19. Also, why do you need an output of three identical rows?

Maybe a different re-arrangement of the data would work better. Please tell us what analyses/plots/tables you would create once you have the data re-arranged as you want?

Re: Combine the variable values vertically

NewUsrStat — Sat, 16 May 2026 15:09:00 GMT

Thank you very much for your help. The reason why I have to replicate rows is because the dataset contains other variables with different values and for the subsequent analysis I have to apply different types of filters.

Re: Combine the variable values vertically

PaigeMiller — Sat, 16 May 2026 15:10:10 GMT

@NewUsrStat wrote:
Thank you very much for your help. The reason why I have to replicate rows is because the dataset contains other variables with different values and for the subsequent analysis I have to apply different types of filters.

Unfortunately, this does not answer my question about what analyses/plots/tables are you trying to create.

Re: Combine the variable values vertically

Tom — Sat, 16 May 2026 15:14:06 GMT

You will need to make NEW variable(s).

data want ;
  set have end=eof;
  length newreason $200 ;
  retain newreason;
  newreason = catx(',',newreason,reason);
  if eof; 
  drop reason;
run;

If the data is grouped by something, perhaps your ID variable, then this can be simplified by by placing the SET inside a DO loop and using a BY statement. This will eliminate the need for the RETAIN statement.

data want;
  do until (last.id);
     set have;
     by id;
     length newreason $200;
     newreason=catx(',',newreason,reason);
  end;
  drop reason;
run;

To output multiple copies you could change the DO statement to count the number of observations and then add a second DO loop around an output statement.

data want;
  do _n_=1 by 1 until (last.id);
     set have;
     by id;
     length newreason $200;
     newreason=catx(',',newreason,reason);
  end;
  do _n_=1 to _n_;
    output;
  end;
  drop reason;
run;

But if there are other variables besides ID and REASON you want to keep then instead just add another DO loop with and additional SET statement.

data want;
  do until (last.id);
     set have;
     by id;
     length newreason $200;
     newreason=catx(',',newreason,reason);
  end;
  do until (last.id);
     set have;
     by id;
     output;
  end;
  drop reason;
run;

And to handle multiple variables just use arrays.

data want;
  do until (last.id);
     set have;
     by id;
     array reason[10];
     array newreason[10] $200;
     do over reason;
       newreason=catx(',',newreason,reason);
     end;
  end;
  do until (last.id);
     set have;
     by id;
     output;
  end;
  drop reason1-reason10;
run;

If you don't like using the deprecated DO OVER syntax then just add explicit indexes instead.

     do _n_=1 to dim(reason);
       newreason[_n_]=catx(',',newreason[_n_],reason[_n_]);
     end;

Re: Combine the variable values vertically

NewUsrStat — Sat, 16 May 2026 16:29:52 GMT

Thank you very much for your help. Unfortunately the code that refers to the array does not work. But, anyway, how it become if the value of the variable is repeated identical?

Re: Combine the variable values vertically

Tom — Sat, 16 May 2026 17:47:01 GMT

If the values repeat then they will appear multiple times.

If the goal is to get the distinct values then you would need to make the logic more complex so that the current reason is only added when it does not already appear.

do _n_=1 to dim(reason);
  if not findw(newreason[_n_],reason[_n_],',','t') then 
   newreason[_n_]=catx(',',newreason[_n_],reason[_n_])
  ;
end;

If you don't care about the case then add the i modifier into addition to the t modifier in the FINDW() function call.

But with these extra complication it might be better to use multiple passes so the logic can be simpler and easier to follow. So first build the list of unique values. Then build the concatenations. And finally remerge it with the original data.

* Make TALL dataset with all of the reasons from all of the reason variables ;
proc transpose data=have out=step1;
   by id;
   var reason1-reason10;
run;
* Eliminate duplicates ;
proc sort data=step1 out=step2 nodupkey ;
   by id _name_ col1 ;
run;
* Concatenate ;
data step3;
   do until (last._name_);
      set step2;
      by id _name_;
      length list $200 ;
      list = catx(',',list,col1);
   end;
   drop col1;
run;
* Turn back into separate variables;
proc transpose data=step3 out=step4 ;
   by id;
   id _name_;
   var list ;
run;
* Combine with original data ;
data want;
   merge have(drop=reason1-reason10) step4;
   by id;
run;

Re: Combine the variable values vertically

ballardw — Sat, 16 May 2026 19:46:02 GMT

@NewUsrStat wrote:
Thank you very much for your help. Unfortunately the code that refers to the array does not work. But, anyway, how it become if the value of the variable is repeated identical?

What does "array does not work"?

If code throws an error then provide the log with the code and all the messages. Copy the text from the log then on the forum open a text box in a message using the </> icon above the message window and paste the code.

If the result is not as expected then provide the data used in the form of a working data step and then provide more detail as to the expected result for the example data.

I am afraid that a generic "does not work" is very unhelpful in diagnosing problems.