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
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.