use Net::SFTP::Foreign::Constants qw(:error :status);
...

unless ($sftp->get($remote, $local)) {
    if (($sftp->error == SFTP_ERR_REMOTE_STAT_FAILED or
         $sftp->error == SFTP_ERR_REMOTE_OPEN_FAILED) and
         $sftp->status == SSH2_FX_NO_SUCH_FILE) {
        say "Remote file does not exist!";
    }
    else {
        say "Transfer failed";
    }
    die $sftp->error;
}