He is famous for a soccer player. - This implies he is only as famous as a soccer player could be. So not as famous as an actor may be, but famous for someone who plays soccer. He is famous as a soccer player. - This would mean he is famous for playing soccer. Soccer is what brought him fame.

The latter one is the correct one. He is famous as a soccer player.