9/11 had such a profound impact on the U.S. because it was spectacular, it was unprecedented, and it happened here. And, thanks to the 24/7 cable news cycle, we watched it unfolding, live, from our living rooms.
Anytime you have a large number of fatalities occurring from a single spectacular event, it will have a stronger emotional impact than a much higher cumulative tally of deaths over time. That's why airliner crashes, for example, are newsworthy and annual statistics are not -- those 100, 200, 300 deaths may be statistically a drop in the bucket compared to the annual deaths from car crashes, cancer, or whatever, but they occurred in a single, dramatic event.
The notion of using airplanes, and civilian airliners at that, as flying bombs was also not a possibility that was in the popular consciousness, not even as a plot element in an action movie. (How many people commented, on 9/11 and in the days following, that it all seemed unreal, like watching a movie and not reality?) And crash those planes into three of the most well-known, high-profile buildings in the world (the two WTC towers and the Pentagon), with a fourth crash into the White House or the Capitol (depending on who you believe) prematurely thwarted, and you have the ingredients for a real-life spectacular that will have a profound impact, regardless of how the numbers stack up statistically.
And it happened on U.S. soil. Prior to 9/11, with the possible exception of the OKC bombing, large scale terrorist attacks were something that happened in those "other" countries around the world. And with the perpetrators being "foreigners" (as opposed to a domestic malcontent like McVeigh and whatever conspirators he may or may not have had, depending on what you believe), and it's not hard to fathom the almost immediate adoption of the "America is under attack" and "we are at war" memes that were so adroitly exploited by the government.
Finally, the smug xenophobia and self-centeredness of Americans played a role. Why do you think a domestic plane crash, even a smaller commuter plane with fewer than 100 souls on board, gets hours of constant, live coverage on CNN while a jumbo jet with hundreds aboard crashing halfway around the world merits but a sentence or two at the hourly update? Think of the impact Hurricane Katrina had while killing fewer than 2000, compared to the Asian tsunami that killed 250,000 five years ago. Now consider how much attention, concern, and TV time were devoted to both. Sure, the Pacific tsunami did get some screen time, especially now that the ubiquitous presence of video cameras in average people's hands gave us some shaky, dramatic, horrifying footage to see. (Though I strongly suspect that if there had been no video at all, the event would have been even more marginalized on U.S. media.) But with the exception of a handful of Western tourists caught up in the disaster, those quarter million souls are "other" people..."fer'iners"...you know, them people that dress weird and talk funny and don't look like us. On the scale of emotional involvement, a couple thousand American lives merits an "OMG, this is horrible, something must be done" while 250,000 Indonesians, Sri Lankans, Thais, et. al. elicits an almost Seinfeldesque "Ah, that's a shame....wonder what's on HBO right now..."
So, it's not sheer numbers that determine what impact death has on a culture; it's all about context. Who got killed, where, how and why.