data have;
input cust $ month apple $ orange $ melon $;
datalines;
1 1 Y . .
1 2 . Y Y
1 3 . . .
2 1 . . .
2 2 Y . .
2 3 . . Y
;
run; I am having a dataset with purchasing record over 100 product in total of 3 months. I would like to consolidate them into one record for each customer. If the customer have purchase that product in any one of the three months, it would show 'Y' in the result dataset. The result should be like this: Have: cust month apple orange melon 1 1 Y 1 2 Y Y 1 3 2 1 2 2 Y 2 3 Y Want: cust apple orange melon 1 Y Y Y 2 Y Y As there are over 100 dynamic columns in the real dataset. I was trying to put all the column into macro variable. However, I do not know how I could utilize these macro variable. proc sql noprint;
select NAME into :_PRODUCT from dictionary.columns
where memname="HAVE"
and libname = "WORK" and (name not in ("CUST","MONTH");
quit; I think it would be more effective if I could utilize macro variable as I do not need to manually input all the column name when I am consolidating the records Thanks in advance.
... View more