how can we print the expected output using characters function
DATA TEST;
INPUT DEPARTMENT $ PRODUCTS $;
DATALINES;
D1 X1
D1 X2
D2 Y1
D2 Y2
D2 Y3
D3 Z1
D3 Z2
D3 Z3
D3 Z4
;
RUN;
expected output
Department Products
D1 X1,X2
D2 Y1,Y2,Y3
D3 Z1,Z2,Z3,Z4
DATA TEST;
INPUT DEPARTMENT $ PRODUCTS $;
DATALINES;
D1 X1
D1 X2
D2 Y1
D2 Y2
D2 Y3
D3 Z1
D3 Z2
D3 Z3
D3 Z4
;
RUN;
data want;
length PRODUCTS $100.;
set test(rename=(PRODUCTS=_PRODUCTS));
by DEPARTMENT _PRODUCTS;
retain PRODUCTS;
if first.DEPARTMENT then PRODUCTS=_PRODUCTS;
else PRODUCTS=catx(',',PRODUCTS,_PRODUCTS);
if last.DEPARTMENT;
drop _PRODUCTS;
run;
DATA TEST;
INPUT DEPARTMENT $ PRODUCTS $;
DATALINES;
D1 X1
D1 X2
D2 Y1
D2 Y2
D2 Y3
D3 Z1
D3 Z2
D3 Z3
D3 Z4
;
RUN;
data Want(rename=(products_=products));
if _n_=0 then do;
set test;
end;
Length products_ $12.;
products_=' ';
do until(last.DEPARTMENT);
set test;
by department;
products_=catx(',',products_,products);
end;
drop products;
run;
DATA TEST;
INPUT DEPARTMENT $ PRODUCTS $;
DATALINES;
D1 X1
D1 X2
D2 Y1
D2 Y2
D2 Y3
D3 Z1
D3 Z2
D3 Z3
D3 Z4
;
RUN;
proc transpose data=test out=t(drop=_name_);
by department;
var products;
run;
data want;
set t;
length products $50;
products=catx(',',of col:);
drop col:;
run;
If all you really want to do is PRINT the data, then a single DATA _NULL_ step works:
DATA TEST;
INPUT DEPARTMENT $ PRODUCTS $;
DATALINES;
D1 X1
D1 X2
D2 Y1
D2 Y2
D2 Y3
D3 Z1
D3 Z2
D3 Z3
D3 Z4
run;
data _null_;
file print;
set test;
by department;
if first.department then put / department @5 products @;
else put +(-1) ',' products @;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.