Creating a new field

[SASMom2]

I need help with creating a new field using values in an existing column. I have a dataset with two columns, Customer and Customer_Number. Below is a sample dataset. I need to create a new field Customer_Number_Multi using the values in the Customer_Number column. I also need single quotes around each value and comma as a seperator. As you can see some Customers have one Customer Number and some have multiple Customer Numbers. The Customer_Number_Multi field should have all Customer_Number values for each Customer. So, for an example, for Customer ABC should have all, 123, 234 and 567 Customer_Numbers in Customer_Number_Multi. Thanks for your help.

Customer	Customer_Number	Customer_Number_Multi
ABC	123	'123', '234', '567'
ABC	234	'123', '234', '567'
ABC	567	'123', '234', '567'
DEF	321	'321'
GHI	345	'345', '678'
GHI	678	'345', '678'
JAK	256	'256'

[mklangley]

Here's one way:

data have;
    input Customer $ Customer_Number $;
    datalines;
ABC 123
ABC 234
ABC 567
DEF 321
GHI 345
GHI 678
JAK 256
    ;
run;

data intermediate;
    set have;
    length Customer_Number_Multi $32.;
    by customer;
    retain Customer_Number_Multi;
    if first.customer
        then Customer_Number_Multi = "";
    Customer_Number_Multi = catx(", ", Customer_Number_Multi, "'"||strip(customer_number)||"'");
    if last.customer;
run;

proc sql;
    create table want as
    select h.*
          ,i.Customer_Number_Multi
    from have h
    left join intermediate i
        on h.customer = i.customer;
quit;

 * Note: the code above assumes your input dataset is sorted by customer (like your sample data) and that any duplicate customer_numbers should be repeated in customer_number_multi.

Output:

[SASMom2]

Thanks for your reply. I will let you know if it works.

[Tom]

Do you really want to remerge the result back into all of the original observations?

Wouldn't be more useful to just have one observation per customer.

data want;
   do until (last.customer);
     set have;
     by customer ;
     length Customer_Number_Multi $200 ;
     customer_number_multi=catx(',',customer_number_multi,quote(cats(customer_number),"'"));
  end;
  keep customer customer_number_multi;
run;