Help using Base SAS procedures

CATX in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

CATX in proc sql

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


Accepted Solutions
Solution
‎05-19-2015 10:28 AM
Super User
Super User
Posts: 7,417

Re: CATX in proc sql

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;

View solution in original post


All Replies
Solution
‎05-19-2015 10:28 AM
Super User
Super User
Posts: 7,417

Re: CATX in proc sql

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;

Super User
Posts: 6,964

Re: CATX in proc sql

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 1055 views
  • 3 likes
  • 3 in conversation