As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.
Here are some tips for How to share your SAS knowledge with your professional network.
Using SQL to transpose vertical to horizontal
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:
- 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 <email@example.com> 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 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;
- Efficient One-Row-per-Subject Data Mart Construction for Data Mining
- Gerhard Svolba, PhD, SAS Austria