Sir Dr Frank Atherton said it had been a "privilege" to act as Wales' chief medical officer