Using SQL to transpose vertical to horizontal

From sasCommunity
Jump to: navigation, search

It's a Bird, It's a Plane, It's SQL Transpose!

Author: Ted Conway


Ever wish you could easily transpose and summarize monthly data using just PROC SQL? Regardless of whether the data is stored in a SAS dataset or other relational databases? Even if scores of variables for dozen of months are involved? This paper tackles the task of data denormalization with a simple macro that uses PROC SQL to take a "vertical" table with multiple rows per subject and transform it into a "horizontal" table containing one row per subject with aggregated monthly values.


Much to the chagrin of Data Modelers and DBAs everywhere, sometimes analysis and reporting is a lot easier with a one-row-per-subject table than a multiple-rows-per-subject table. On his Statistical Computing weblog, Wensui Liu describes three ways of converting a “long” table to a “wide” table using SAS:


Wensui’s PROC SQL solution, repeated below, is a logically appealing technique that has the added bonus of being able to be used with SAS datasets as well as other relational databases:

Harvested from SAS-L

  • Subject: Re: Transpose data using SQL
  • On Wed, Jun 13, 2012 at 8:32 PM, Ben <> wrote:

Code harvesting and polishing by: Ronald_J._Fehd


 /*    name: long2wide
description: rotate a vertical table to horizontal by Id
             database vocabulary: denormalize
    purpose: SQL trick
It's a Bird, It's a Plane, It's SQL Transpose!
Ted Conway 2008
%macro long2wide
       (data   = _last_
       ,by     =
       ,months =
       ,vars   =
       ,out    = _data_
proc sql; create table &out as
          select &by
          %let dv=%scan(&months,1);
          %let i=1;
          %let v=%scan(&vars,1);
          %do %while("&v"^="");
              %let j=2;
              %let mo=%scan(&months,2," ");
              %do %while("&mo"^="");
                  %let lmo="01%scan(&mo,1,"-")"d;
                  %let hmo="01%scan(&mo,2,"-")"d;
                  %if &hmo="01"d %then %let hmo=&lmo;
                  %let nmo=%sysfunc(intck(month,&lmo,&hmo));
                  %do m2=0 %to &nmo;
                      %let mo1=%sysfunc(intnx(month,&lmo,&m2),date9.);
                      %let mo2=%sysfunc(intnx(month,&lmo,&m2),yymmn6.);
                      , sum(case when &dv="&mo1"d then &v end) 
                      as &v&mo2
                  %let j=%eval(&j+1);
                  %let mo=%scan(&months,&j," ");
              %let i=%eval(&i+1);
              %let v=%scan(&vars,&i);
          from &data
          group by &by
          order by &by;
%mend long2wide;