..... but with no immediate result!
Like you I cant see him in 1901 (other Sproats around in the area, presumablty related) nor can I see a death or marriage.
That leaves either gross mistranscription, missed off census, or emigration as the main answers!
Maybe someone can check the outgoing passenger lists on FindMyPast for you if you dont have your own access.
Incoming lists on Anc have a David Sproat arriving from New York in 1911, no age for him though. Who knows, maybe he left n came back for a visit?