Number alignment and spacing in tables with parentheses, text, decimals, and integers

You need to use braces around all of your repeated column specifier: *{5}{S[...]} not *{5}S[...] to apply the options to every column. That leads to an answer that looks very similar to Mico's:

\documentclass[12pt,letterpaper,fleqn]{article}

\usepackage{booktabs}
\usepackage{siunitx}
\sisetup{parse-numbers = false}

\begin{document}

\begin{table}[h]
\centering
\begin{tabular}{@{}l *{5}{S[table-format = +1.5]}@{}}
\toprule
& \multicolumn{1}{c}{AA} & \multicolumn{1}{c}{AA} & \multicolumn{1}{c}{AA} & \multicolumn{1}{c}{BB} & \multicolumn{1}{c}{CC} \\
& \multicolumn{1}{c}{(1)} & \multicolumn{1}{c}{(2)} & \multicolumn{1}{c}{(3)} & \multicolumn{1}{c}{(4)} & \multicolumn{1}{c}{(5)} \\
\midrule
X & -0.333^{***} & -0.222^{***} & -0.776^{***} & -0.333^{***} & -0.662^{***} \\ 
  & (0.003) & (0.002) & (0.026) & (0.048) & (0.001) \\ 
Y & & -0.004 & & \\ 
  & & (0.008) & & \\ 
Z & 0.111 & 0.122 & 0.123 & 0.122 & 0.133 \\ 

Obs & \multicolumn{1}{c}{$55,000$} & \multicolumn{1}{c}{$56,000$} & \multicolumn{1}{c}{$56,000$} & \multicolumn{1}{c}{$56,000$} & \multicolumn{1}{c}{$56,000$} \\ 
FE & & & \multicolumn{1}{c}{Yes} & \multicolumn{1}{c}{Yes} & \multicolumn{1}{c}{Yes} \\
Fstat & & 0.225 & 0.221 & 0.222 & 0.220 \\
\bottomrule
\end{tabular}
\end{table}

\end{document}

(When parse-numbers = false is set, siunitx uses the same approach as dcolumn for alignment. So the main issue is making sure it knows how many digits to leave space for.)

In your test document, the only use of the siunitx package seems to be its S column type, to align numbers in columns on their decimal markers. (Otherwise, why issue the instruction \sisetup{parse-numbers=false}?) If that's the case, you could simplify the document setup considerably by employing the dcolumn package, whose one and only job is -- you guessed it -- helping to align numbers in columns on their decimal markers.

Oh, and please use \toprule and \bottomrule instead of \midrule\midrule.

\documentclass[12pt,letterpaper,fleqn]{article}
\usepackage{booktabs}

\usepackage{dcolumn}
\newcolumntype{d}[1]{D..{#1}}

\newcommand\mc[1]{\multicolumn{1}{c}{#1}} % handy shortcut macro
\begin{document}

\begin{table}[h]
\centering  %\small  % "\small" doesn't seem necessary
\begin{tabular}{@{} l *{5}{d{2.5}} @{}}
\toprule
& \mc{AA}  & \mc{AA}  & \mc{AA}  & \mc{BB}  & \mc{CC} \\
& \mc{(1)} & \mc{(2)} & \mc{(3)} & \mc{(4)} & \mc{(5)} \\
\midrule
X & -0.333^{***} & -0.222^{***} & -0.776^{***} & -0.333^{***} & -0.662^{***} \\ 
  & (0.003) & (0.002) & (0.026) & (0.048) & (0.001) \\ 
Y & & -0.004  & & \\ 
  & & (0.008) & & \\ 
Z & 0.111 & 0.122 & 0.123 & 0.122 & 0.133 \\ 

Obs & \mc{$55{,}000$} & \mc{$56{,}000$} & \mc{$56{,}000$} & \mc{$56{,}000$} & \mc{$56{,}000$} \\ 
FE & & & \mc{Yes} & \mc{Yes} & \mc{Yes} \\
F-stat & & 0.225 & 0.221 & 0.222 & 0.220 \\
\bottomrule
\end{tabular}
\end{table}

\end{document}