CATX in proc sql

[SAS_NUBI2015]

Hello together,

I want to concatenate the values of the columne tax_nr (alphanumeric with 12 character length) on my unique primary key. For example:

Table Customer:

Cust_Nr                 tax_Nr

123456789            12345B

123456789            12345C

123456789            12345D

553456789             22235B

553456789             22235K

553456777             33239Z

The result shall seem after selection as follows:

Cust_Nr                  number_of_pk              tax_Nr

123456789             3                                   12345B, 12345C, 12345D

553456789              2                                   22235B, 22235K

553456777              1                                   33239Z

I tried it by following syntax:

select Cust_Nr,

           count(Cust_Nr)      AS number_of_pk,

          case when count( Cust_Nr) > 1 then put(input(catx(",",tax_Nr),best12.),best12.)

          else tax_Nr

          end      AS  tax_Nr_NEW

From t1.customer

group by Cust_Nr;

However it doesn't work. Any clues about what shall I change in syntax? Thank you in advance.

Best regards

[RW9 · Accepted solution]

SQL is not best suited to the task you describe.  It is built to deal with normalised data, not transposed, which is what you want as output.  I would suggest:

data want;

     set have;

     length tax_nr_string $200;

     retain tax_nr_string number_of_pk;

     by cust_nr;

     if first.cust_nr then do;

          tax_nr_string=tax_nr;

          number_of_pk=1;

     end;

     else do;

          tax_nr_string=catx(',',tax_nr_string,tax_nr);  /* If you get blanks then put a strip() function around each */

          number_of_pk=number_of_pk+1;

     end;

     if last.cust_nr then output;

run;

[Kurt_Bremser]

catx(",",tax_Nr) will yield one of your tax_Nr, which is not entirely numeric, therefore the input with best12. will fail.

My solution for such tasks is a data step:

proc sort data=have;

by Cust_Nr;

run;

data want (keep=Cust_Nr number_of_pk tax_Nr_NEW);

set have;

by Cust_Nr;

length tax_nr_NEW $ 50; *size this sufficiently big to hold all possible tax_Nr;

retain

  tax_Nr_NEW

  number_of_pk

;

if first.Cust_Nr then do;

  tax_Nr_NEW = '';

  number_of_pk = 0;

end;

tax_Nr_NEW = catx(',',trim(tax_Nr_NEW,trim(tax_nr));

number_of_pk + 1;

if last.Cust_Nr then output;

run;