A two-step transpose approach to reshape data

New in SAS 9.2, the TRANSPOSE procedure accepts multiple IDs in its ID statement. More than one IDs would automatically concatenate together as the new variable names. Previously, Proc Transpose usually only allows one ID. As the result, the concatenation of variable names has to be done by DATA step array in SAS 9.1 or earlier versions. This change would bring more flexibility to reshape data to any desired structure. In this case, a small file with date, gender and 3 credit records is transformed to a more flat data structure, only corresponding to the date. Gender would be moved from row name to column name, and consequently several new variables would be created to combine old variables: credit and gender.

A variety of methods in SAS can realize the reshaping purpose. The coding can follow the principle: first accumulate the numeric values in a single column; second expand them with its accompanying IDs as new variable names. As usual, Proc SQL is always the first choice to aggregate data by its Group By statement. In this example, splitting and combining did the trick but needs some more coding. To increase efficiency, building a macro may be useful. If the programming intention is to report, Proc Report has the magic power to display sub-categories with least code. However, variable names have to be re-defined in the following steps.

Two-step transpose by Proc Transpose is intuitive to change data structure horizontally. And it’s pretty extensible to even more complex data structure.

*******(0) INPUT RAW DATA***********;
data raw;
format date mmddyy10.;
input sex: $1. Date: mmddyy10. Credit1 Credit2 Credit3;
cards;
M       01/01/2011    600     610  650
M       01/02/2011    500     510   730
F       01/01/2011    700     710    820
F       01/02/2011    400     410   500
;
run;

*********(1)TWO-STEP TRANSPOSE***************;
******NOTE: only works in SAS 9.2*********;
proc sort data=raw out=raw_s; 
   by date sex;
run;

proc transpose data=raw_s out=raw_t;
   var credit:;
   by date sex;
run;

proc sort data=raw_t out=interim; 
   by date; 
run;

proc transpose data=interim out=final1(drop=_name_);
   var col1;
   by Date;
   id sex _name_ ;
run;

********(2)DATA STEP ARRAY: AN ALTERNATIVE FOR TWO-STEP TRANSPOSE******;
data arrout;
   set raw;
   array cr[*] credit:;
   do i=1 to dim(cr);
      cred=cr[i];
      cred_name=cats(sex,vname(cr[i]));
      output;
   end;
   keep date cred cred_name;
run;

proc sort data=arrout out=arrout_s; by date;run;
/*NOTE: a following transpose would be better than another data step array*/
proc transpose data=arrout_s out=final2(drop=_name_);
   by date;
   var cred;
   id cred_name;
run;

*********(3)PROC SQL AND MACRO********;
%macro reshape(max);
   %do i=1 %to &max;
      proc sql;
      create table out&i as
      select a.date,  a.credit&i  as Mcredit&i , b.credit&i  as    Fcredit&i
      from raw(where=(sex='M')) as a , raw(where=(sex='F'))  as b
      where a.date=b.date
      ;quit;
   %end;
   data final3;
   %do j=1 %to &max;
      set out&j;
   %end;
run;
%mend reshape;
%reshape(3);

******(4)PROC REPORT*********;
proc report data=raw nowd out=final4 ;
   column date sex,(credit:);
   define date/group;
   define sex/across;
run;

*****(5) ANOTHER EXAMPLE******;
data have;
    input id $ num;
    cards;
    A 1
    A 2
    A 3
    A 4
    B 1
    B 2
    B 3
    B 4
    B 5
    C 1
    ;
run;

proc transpose data=have out=temp;
    by id;
    var num;
run;

proc transpose data=temp out=want(drop=_name_);
    by _name_;
    id id;
    var col:;
run;
*********End of the program****Tested on 21Jan2011********;

Find the 'right' SAS functions

How many functions SAS has? Well, it sounds like a job interview question. For SAS 9.2, by querying the system dictionary (sashelp.vfunc or dictionary.functions), the exact answer is 946, including all functions and call routines. There are two types - unicode/bit based on input argument, while three types –numeric/character/bitwise based on output argument. Again according to their usage[1], the common SAS functionscan be categorized into several types: array(3), bitwise logical operation(3), PERL regular expression(11), character(91), time(38), descriptive statistics(32), random number(22), probability(18) , mathematics(36), finance(32), etc.

Some functions have evolved for several generations Since SAS has development history of more than 40 years. For example, there are 6 functions from SAS dictionary for random number generator from normal distribution, including ‘normal’, ‘rannor’ and its call routine, ‘rannorm’ and its call routine, and ‘rand’. All functions need seeds to produce random numbers and the random number queue can be replicated if with the same seed. The difference is that the latest one ‘rand’ has astronomical possibilities of seeds, while the older types only contains merely 2 trillion seeds that can cause dependence among various random number queues.

SAS handles data with rows as units (SAS calls row as observation), which is a unique characteristics, while most other software packages tend to process data with columns or vectors. Thus, many summarization functions in Data step only work on the ‘right’ by combining all variables in a row. As for vertical summarization, the SAS procedures are more appropriate, such as Proc Summary, Proc Means or Proc report. In a word, if we prefer SAS Data step, a transposition may be necessary.

Reference: 1. SAS 9.2 Language Reference: Dictionary, Third Edition. SAS Publishing. 2009.
2. Ron Cody. SAS Functions by Example, Second Edition. 2010.

/**********************AUTHOR(DAPANGMAO)----HCHAO8@GMAIL.COM***********************************;
*(1)CALCULATE THE COMPOSITION OF SAS FUNCTIONS*/
proc sgplot data=sashelp.vfunc;
vbar fnctype/barwidth=0.5
transparency=0.2 ; 
run;

proc sgplot data=sashelp.vfunc;
vbar source;
run;
proc sgplot data=sashelp.vfunc;
scatter x= source y=fnctype;
run;
proc sql;
select *
from sashelp.vfunc
where lowcase(fncname) in ('rannorm', 'rannor', 'normal', 'rand');
quit;
proc freq data=sashelp.vfunc;
tables fnctype*source/nopercent nocum norow nocol;
run;

/*(2)COMPARE SERVAL RANDOM FUNCTIONS*/
data one;
call streaminit(1234);
do i=1 to 10000;
x1=rannorm(1234);
x2=rannor(1234);
x3=normal(1234);
x4=rand('normal');
output;
end;
run;
data two;
seed1 = 1;
seed2 = 3;
seed3 = 5;
seed4=7;
do i = 1 to 10000;
call rannor(seed1, x1);
call rannor(seed2, x2);
call rannor(seed3, x3);
call rannor(seed4, x4);
output;
end;
run;
%macro test(input);
proc sgscatter data = &input;
title 'Independence test';
plot x1*x2 x1*x3 x3*x2 x1*x4 x2*x4 x3*x4 / markerattrs = (size = 1);
run;
%mend test;

/*(3)PICK OUT THE LARGEST THREE FROM VARIOUS TRANSACTIONS*/
data test;
attrib amt informat=dollar10.2 format=dollar10.2;
do id=1 to 10;
times=ceil(100*ranuni(12345));
do i=1 to times;
amt=10000*ranuni(123);
output;
end;
end;
drop i times;
run;
proc sort data=test out=test1; 
by id descending amt;
run;
data test2;
do _n_=1 by 1 until(last.id);
set test1 ;
by id;
if _n_<4 then output;
end;
run;
proc transpose data=test out=test3(drop=_name_);
by id notsorted;
var amt;
run;
proc sql;
select name into: vname separated by ', '
from sashelp.vcolumn
where libname='WORK'
and memname='TEST3'
and name contains 'COL'
;quit;
%put &vname;
data test4;
set test3;
call sortn(of &vname);  
vstd=std(of &vname);
vmax=max(of &vname);
vmean=mean(of &vname);
vmedian=median(of &vname);
vrange=range(of &vname);
vnum=n(of &vname);
vmissing=nmiss(of &vname);
no1=largest(1, of &vname);
no2=largest(2, of &vname);
no3=largest(3, of &vname);
run;

Partial sorting

SAS can also do the partial sorting like Excel does. Since SAS deals with observation as basic unit, subsetting and merging is necessary before a partial soring by Proc Sort.

data one;
 input @1 var1 1. @3 var2 $1. @5 var3 $1. @7 var4 1. @9 var5 $1.;
 cards;
1 Y A 7 Z
3 J A 6 T
2 E A 5 S
7 Z A 4 Q
4 Q B 3 J
6 T B 2 E
5 S B 1 Y
;run;

proc sort data=one out=one_s(drop=var4 var5);
 by descending var1;
run;

data two;
 set one_s;
 set one(keep=var4 var5);
run;