Use PROC SQL to reshape data

SQL is widely used for data manipulation in relational database management systems. With the help of a macro loop, SAS's SQL procedure can perform a lot of duties, such as reshaping data. It can realize the same functionality as PROC TRANSPOSE and DATA step ARRAY. Hereby I use an example of SASHELP.CLASS to show how to transpose data either from wide to long or from long to wide. The two macros wrapping up PROC SQL are both based on the "split-then-merge" logic.

data class;
   set sashelp.class;
run;

%let var_list = age weight height;

proc transpose data = class out = long_proctranspose;
   by name sex;
   var &var_list;
run;

proc transpose data = long_proctranspose out = wide_proctranspose;
   by name sex;
   id _name_;
   var col1;
run;

%macro wide2long();
   proc sql;
      create table long
         (name char(20), sex char(1), var_name char(10), var_value num)
      ;
      %do i = 1 %to 3;
         %let var = %scan(&var_list, &i);
         insert into long
         select name, sex, "&var" as var_name, &var as var_value
         from class
         ;
      %end;
   quit;
%mend;
%wide2long;

%macro long2wide();
   %do i = 1 %to 3;
      %let var = %scan(&var_list, &i);
      proc sql;
         create table _&var as
         select name, sex, var_value as &var
         from long 
         where var_name = "&var"
         ;
      quit;
   %end;
   data wide;
      %do j = 1 %to 3;
         %let var = %scan(&var_list, &j);
         set _&var;
      %end;
   run;
   proc datasets nolist;
      delet _:;
   quit;
%mend;
%long2wide;