The Sonnenschein-Mantel-Debreu results, what are the implications for macroeconomics? I have always wondered why such a negative result such as Sonnenschein–Mantel–Debreu theorem (SMD) theorem is usually ignored or disregarded by practitioners, specially in macro. 

The result roughly says that there is no deep empirical implications of the rationality assumptions when aggregated because the aggregate demand itself only inherits Walras' law and homogeneity of degree zero. The lack of empirical implications can be translated to the lack of identification and so we cannot hope to recover the underlying preferences from an aggregate demand setup as much of the cross-country empirical macro or calibration macro seems to try. For additional discussion of the importance of the SMD theorem, see this piece. 

In sum what are the implications of the SMD theorem for macroeconomics.
This is somewhat broad and argumentative question, but I'll try to provide one possible answer (disclaimer: I'm not a GE theorist). SMD theorem states that imposing only the abstract structure of general equilibrium has few empirical implications. Fair enough. But it's incorrect to say that 


  we cannot hope to recover the underlying preferences from an aggregate demand setup as much of the cross-country empirical macro or calibration macro seems to try.


because once we impose additional assumptions about preferences, technologies and endowments, we do obtain additional empirical predictions (and of course, different sets of assumptions will lead to different predictions). This is precisely what economists are doing most of the time! It's not 1950's anymore - these days, there is very little research that tries to arrive at general results from abstract axioms. Most theoretical work in macroeconomics will present specific model, derive its qualitative and quantitative implications and often will include an empirical component.

Another often-heard argument, present e.g. in the linked piece, is that SMD theorem shows GE models are in general unstable or indeterminate, and thus we must resort to unrealistic restrictions, such as representative agent assumption, to guarantee that our models are well-behaved. But this confuses necessary and sufficient condition: yes, assuming representative agent is sufficient to obtain well-behaved aggregate demand function, but that doesn't mean all, or even most, models with heterogeneity are somehow automatically unstable. And a logical possibility of unstable equilibrium says nothing about whether such situation is empirically relevant (maybe it is, but critics usually provide no evidence for such claims).