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;
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 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.