You want a divisorial contraction $Y \to X$ on a smooth 3-fold Y. Such contractions have been classified by Mori in his paper "3-folds whose canonical bundles are not numerically effective", thm 3.3. There are exactly the following possibilities:

the smooth blow-up of a point; in this case $S$ is isomorphic to $\mathbb{P}^2$ with normal bundle $\mathcal{O}(-1)$;

the smooth blow-up of a curve, in this case $S$ is a ruled surface whose normal bundle restricted to the ruling has degree $(-1)$; this is the situation described by Donu Arapura in his answer;

the contraction of a plane $S$ with normal bundle $\mathcal{O}(-2)$; in this case the
surface $X$ has an isolated singularity isomorphic to the quotient of $\mathbb{A}^3$ by the involution $(x,y,z) \to (-x, -y, -z)$;

the contraction of a smooth quadric $S$ whose rulings are numerically equivalent; in this case the image of $S$ is a single point, which is a singular point for $X$;

the contraction of a singular quadric $S$; again, the image of $S$ is a point in $X$.

Summing up, if you want that $X$ is smooth and the image of $S$ is a curve, the only possibility is 2.

In the case where $Y$ is a smooth 4-fold and $S$ is a smooth surface, the answer can be found in the paper of Kawamata "Small contractions of four-dimensional algebraic manifolds": in this case the only possibility is that $S$ is the disjoint union of copies of $\mathbb{P}^2$, with normal bundle $\mathcal{O}(-1) \oplus \mathcal{O}(-1)$