@geds133 wrote:
However there are duplicate results in my table which means the group by has not grouped the desired variables.
Maybe the "duplicates" only look like duplicates, but in fact there are minor differences between the (character) GROUP BY variable values, e.g. due to leading blanks or invisible characters. (With numeric grouping variables tiny rounding errors could cause the differences.)
Example:
proc print data=test;
run;
Result:
Obs x state town
1 -0.1 NY New York
2 22.2 NY New York
3 22.8 NY New York
proc sql;
select state, town, sum(x) as total
from test
group by state, town;
quit;
Result:
state town total
-------------------------------------
NY New York -0.1
NY New York 22.8
NY New York 22.2
No aggregation has occurred, but aren't there duplicate state-town combinations?
Solution:
data test;
length x 8 state $3 town $20;
input x 4. town $char20.;
if _n_=2 then substr(town,4,1)='A0'x;
state='NY'||byte(x+10);
cards;
-0.1 New York
22.2New York
22.8New York
;
proc print data=test;
format state $hex6. town $hex18.;
run;
... View more