topic Re: Group rows and creata a new column with values in comma in SAS Programming

Group rows and creata a new column with values in comma

Ronein — Tue, 06 Mar 2018 07:02:43 GMT

Hello friends,

I want to ask a question please.

I have a data set which includes 4 rows with 2 columns.

X Y

1 1

2 2

2 3

2 4

I want to create following data set from my data set

X Y

1 1

2 2,3,4

Can anyone help how to do it?

Thanks

Ronein

Re: Group rows and creata a new column with values in comma

andreas_lds — Tue, 06 Mar 2018 07:51:01 GMT

This is simple by-group-processing, explained in numerous papers & posts.

Untested, assuming that work.want is sorted by x:

data work.want;
   set work.have;
   by x;

   length Y_List $ 100;
   retain Y_List;

   if first.x then Y_List = ' ';
   Y_List = catx(',', Y_List, Y);
   if last.x then output; 

   drop y;
run;

Re: Group rows and creata a new column with values in comma

Ronein — Tue, 06 Mar 2018 09:21:13 GMT

It is a perfect and very clever solution.

May you please explain the code and steps of process.

I am trying to understand it but couldn't understand.

Thank you so much!

Re: Group rows and creata a new column with values in comma

ballardw — Tue, 06 Mar 2018 23:47:08 GMT

data work.want;
   set work.have;
   by x;  /* assumes data is sorted by this variable
             automatic variables for variables on the 
             by statement are available to indicate the 
             first or last of a group represented by 
             the variable(s)
           */

   length Y_List $ 100; /* specify a length of the target variable
                           long enough to hold the longest expected
                           result
                        */
   retain Y_List;       /* keep the value of the variable between iterations
                           of the data step
                        */

   if first.x then Y_List = ' ';  /* set the target variable to empty at 
                                     the start of each group of X values
                                  */
   Y_List = catx(',', Y_List, Y); /* catx function combines the previous version of the
                                     Y_list variable with the current inserting a comma
                                     note the comma only appears BETWEEN values, not after
                                     the last
                                  */
   if last.x then output;       /* the IF in this form only sends data to the output 
                                   set when the condition is true. The condition
                                   here is that the current X value is the last 
                                   of this group.
                                 */

   drop y;                      /* remove the current value of Y when the data is output*/
run;