title: Using PROC SQL in SAS to transpose a table from long to wide
body: <p>I was reviewing Life Imitates Art: ODS Output Data Sets that Look like Listing Output at <a href="http://www.mwsug.org/proceedings/2013/BB/MWSUG-2013-BB12.pdf" rel="nofollow">http://www.mwsug.org/proceedings/2013/BB/MWSUG-2013-BB12.pdf</a> where the author forces outputted datasets from PROCs to resemble their tabular descriptive output.   The author's code is: </p>
  
  <pre><code>proc print data=sashelp.bweight(obs=5); run;
  
  Proc Tabulate MISSING
  Data=sashelp.bweight
  Out=outequals(drop=_:);
  Class ed smoke;
  Tables ed, smoke /
   box="(Table in ODS Destination)";
  Run;
  
  Proc Print
  Data=outequals noobs;
  Run;
  
  Proc Transpose
  Data=outequals
   Out=twowaytable(drop=_:)
   prefix=smoke;
   By ed;
   ID smoke;
  Var N;
  Run; 
  </code></pre>
  
  <p>I was wondering if there was a pretty way to do this using proc sql because in the past it seemed that proc transpose was inflexible as problems became more complicated.  I came up with a solution (below), but I can't help but wonder if PROC SQL could be used more efficiently.</p>
  
  <pre><code>proc sql ; 
  create table col2_a as 
  select * from outequals where smoke eq 1 and ed = 0 
  outer union 
  select * from outequals where smoke eq 1 and ed = 1 
  outer union 
  select * from outequals where smoke eq 1 and ed = 2 
  outer union
  select * from outequals where smoke eq 1 and ed = 3
  ;
  quit;
  
  proc sql ; 
  create table col2_b as 
  select monotonic() as key, * 
  from col2_a 
  ; quit;
  
  proc print data=col1_b ; run;
  
  proc sql ; 
  create table report as 
  select 
  a.ed as ed,
  a.N as Smokers_HC,
  b.n as NonSmokers_HC
  from 
  col1_b a join col2_b b 
  on a.key eq b.key
  ;quit;
  </code></pre>