title: Transposing rows to columns in SAS or SQL
body: <p>I'm a new SAS/SQL user and I have a dataset which I need to transpose some rows to columns. I think that there is a faster or easier way to do that and I'd like to some advice to you all. My example will better explain my issue:</p>
  
  <p>Here is the dataset I have:</p>
  
  <pre><code>Month   ID     Car      Claim_Type   Cost_of_claim
    1    1243   Ferrari    Collision      12,000
    2    6437   Peugeot    Fire           50,000
    5    0184   Citroen    Stole           3,000
    9    1930   Fiat       Medical         1,000
    3    2934   GM         Liability      20,000
  </code></pre>
  
  <p>And I need to create a dataset like that:</p>
  
  <pre><code>Month   ID     Car    Collision   Fire    Stole   Medical Liability
  1    1243   Ferrari    12,000       0       0       0         0 
  2    6437   Peugeot       0      50,000     0       0         0         
  5    0184   Citroen       0         0      3,000    0         0
  9    1930   Fiat          0         0       0     1,000       0
  3    2934   GM            0         0       0       0      20,000
  </code></pre>
  
  <p>I just transposed some rows to columns...</p>
  
  <p>I was thinking in do something like that to create my new dataset:</p>
  
  <pre><code>proc sql;
  select Month, ID, CAR
    case when Claim_Type = 'Collision' then Cost_of_claim end Collision,
    case when Claim_Type = 'Fire'      then Cost_of_claim end Fire,
    case when Claim_Type = 'Stole'     then Cost_of_claim end Stole,
    case when Claim_Type = 'Medical'   then Cost_of_claim end Medical,
    case when Claim_Type = 'Liability' then Cost_of_claim end Liability
  from my_table;
  </code></pre>
  
  <p>The problem is that a have a huge amount of data and I think that this way might be not too efficient. Also, in my dataset I have much more columns and rows and don't want to type all the possibilities in <code>case when</code> statements as it does not seems to be to easy (or user-friendly) to maintain the code. </p>
  
  <p>Can someone help me in how to solve that?</p>