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

ABSTRACT

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.

INTRODUCTION

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:

  • PROC TRANSPOSE
  • PROC SQL
  • DATA STEP

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 <benpub7@yahoo.com> wrote:

Code harvesting and polishing by: Ronald_J._Fehd

Macro

 /*    name: long2wide
description: rotate a vertical table to horizontal by Id
             database vocabulary: denormalize
    purpose: SQL trick 
http://www2.sas.com/proceedings/forum2008/089-2008.pdf
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
                      %end;
                  %let j=%eval(&j+1);
                  %let mo=%scan(&months,&j," ");
                  %end;
              %let i=%eval(&i+1);
              %let v=%scan(&vars,&i);
              %end;
          from &data
          group by &by
          order by &by;
          quit;
run;          
%mend long2wide;

References