title: ERROR: Subquery evaluated to more than one row in Proc SQL
body: <p>I have a table -</p>
  
  <pre><code>Name    Job Group     Profession
  John    Engineering    Yes
  Shawn   Biology        Yes
  Meg     Biology        No
  Mike    Engineering    Yes   
  Meg     Biology        Yes
  John    Engineering    Yes
  Mike    Biology        Yes
  John    Engineering    Yes
  Meg     Biology        Yes
  </code></pre>
  
  <p>I want to count Profession(either Yes or No) for same names corresponding to Job group. So the output I want is -</p>
  
  <p>Output</p>
  
  <pre><code>Name   Engineering  Biology 
  John      2            1
  Mike      1            1
  Shawn                  1
  Meg                    2
  </code></pre>
  
  <p>I tried with this code but no luck -</p>
  
  <pre><code>CREATE TABLE Profile AS   
  SELECT P.Name
          ,sum(P.Profession = "Yes") as Engineering_Yes
          ,sum(P.Profession = "Yes") as Engineering_No
          ,sum(P.Profession = "No") as Biology_Yes
          ,sum(P.Profession = "No") as Biology_Yes            
  FROM Profile P
  </code></pre>
  
  <p>I tried to add Job Group column using union select but no luck</p>
  
  <p>I am new to Proc SQL.I tried to search same example but cannot find it for Proc SQL. Please help.</p>