Appending in SAS array

[mmatiaspostcz]

Hello there,

this may be a dumb question but I am new to SAS and trying to find out how to solve one problem which in another language like Python I would know (roughtly 🙂 ) how to do.

So my dataset is as follows

ID        product

1         A

1         B

1         C

2         A

2         B

and I want output as follows

ID        product

1         A, B, C

2         A, B

I would like to create an array and iterate tought this dataset and append a product into array every time there is the same ID for customer and when it changes I would like to output that and empty array. But not sure how to append and did not find it 😕

Thank you for any suggestion

[RW9 · Accepted solution]

Arrays in SAS do not work like other languages.  They are references to groups of variables.  What you want here is a retain and concat as you go along.

data want;
  set have (rename=(product=temp));
  length product $200;
  by id;
  retain product;
  product=ifc(first.id,temp,catx(',',product,temp);
  if last.id then output;
run;

If you want to see what it is doing, just remove the if last.id and you will see that the new variable product increases in data contained there each observation, the first obs it is set to temp (renamed from product as output has variable product which is different), after that each obs gets added to the list.

[Astounding]

As @RW9 mentioned, this is not really an array.  Instead, you would be creating a single, long text string.  If you ever wanted to treat it as you would a Python array, and process each element within, you would need to basically undo your text string to get it back to the original, current form of the data.  And hope that none of the original PRODUCT values contains a comma.

The current form of the data is ideal for most types of processing.  Of course it depends on your objective, but if you were to post a little more on where you are heading with this, you are sure to get suggestions about how the current form of the data can be used to get there.