Please use the following codes to generate data: options validvarname=any;
/* user defined */
%let IDList = ID;
%let SumValueList = Balance;
%let CharList = Gender Date Account;
/* dataset - have */
data have;
input ID Gender $ Date ddmmyy10. Balance Account Nonlist;
cards;
1 M 01/01/1990 . 2 9
1 M 01/05/1991 0 2 8
1 F 31/12/2000 800 5 10
;
run;
/* dataset - want */
data want;
input ID Gender $ Date $char32. Balance Account $ Nonlist;
cards;
1 M,F 01/01/1990,01/05/1991,31/12/2000 800 2,5 9
;
run; If the column name is listed ... in the 'IDList' macro variable: such column used to identify similar rows (that need to be concatenated). in the 'SumValueList': sum up all numeric values in such columns. In our example it would be 800 = . + 0 + 800. Please note: missing values '.' treated as 0 only sum up unique values: e.g. 100 80 . 100 would be 180 (100 + 80 + .) rather than 280 in the 'CharList': concatenate all texts, separated by ',' . Please note: the length of the columns is very likely to be changed in order to capture all information. only capture unique values: e.g. 2 2 5 would be 2,5 rather than 2,2,5 Not listed in any of the macro variables above, use the value that appears first. How to achieve it? I've seen other posts with similar problems, but in our case, different columns require different operations. Thanks.
... View more
