options ls=80 nodate pageno=1 NOFMTERR; 

  * Class19_10.sas    output,  transpose, SQL;
  * adapted example from http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm;

   * uses ph6470.econ_long with n=300 records from Class19_10.sas

          family_
   Obs       id      income    year    expenses    debt    group
     1       1        66483    1990      49804     no        A
     2       1        69146    1991      65634     no        A
     3       1        74643    1992      61820     no        A
  ;

 data a;
   set ph6470.econ_long;
   if (family_id=2 and year = 1991) then delete;  

 Proc print data=a (obs=11);
   
 Proc Transpose data=a out=income prefix=income_;
   ID year;
   VAR income;
   BY family_id;
 data wide;
   set income;
    drop _NAME_; 

 proc print data=wide(obs=10);

 data long;
   set wide;
   array inc[6] income_1990 - income_1995; 
   do j = 1 to 6;
      income = inc[j];
      year = j + 1989;
      output;
      end;
   keep family_id income year; 

 proc print data=long(obs=10);

 proc sort data=long;
   by family_id  year; 


 data wide2;
   set long;
   by family_id ; 
   array inc[6] income_1990 - income_1995; 
   retain income_1990 - income_1995;

   if (first.family_id=1) then do j = 1 to 6;
     inc[j] = . ;
     end;
   if year=1990 then income_1990=income;
   if year=1991 then income_1991=income;
   if year=1992 then income_1992=income;
   if year=1993 then income_1993=income;
   if year=1994 then income_1994=income;
   if year=1995 then income_1995=income;
   if (last.family_id=1) then output;
   keep family_id income_1990 - income_1995; 

 proc print data=wide2(obs=10);

  proc print data=income(obs=10);

  ****************** keep group variable *************;

 proc sort data=a;
   BY family_id group;

 Proc Transpose data=A out=B prefix=income_;
   ID year;
   VAR income;
   BY family_id group;

 proc print data=B(obs=10);
 proc print data=A(obs=10);
   var family_id income year group;

 run; quit;/*

 *****************transpose > 1 variable***********;

 Proc Transpose data=a out=income prefix=income_;
   ID year;
   VAR income;
   BY family_id;

 Proc Transpose data=a out=expenses prefix=expense_;
   ID year;
   VAR expenses;
   BY family_id;

 Proc Transpose data=a out=debt prefix=debt_;
   ID year;
   VAR debt;
   BY family_id;

 data wide4;
   merge income expenses debt;
   by family_id;

 proc print data=wide4(obs=2);

 run; quit;/*