topic Re: CATX in proc sql in SAS Procedures

CATX in proc sql

SAS_NUBI2015 — Tue, 19 May 2015 14:20:21 GMT

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

Re: CATX in proc sql

RW9 — Tue, 19 May 2015 14:28:23 GMT

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;

Re: CATX in proc sql

Kurt_Bremser — Tue, 19 May 2015 14:31:10 GMT

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;