Long data to wide data

[onhoaian]

How can I transform the data below from long form to wide form, and extend from 3 stocks to N stocks?

Thank you for your help.

STOCK	DATE	RETURN				DATE	RETURN_S001	RETURN_S002	RETURN_S003
S001	6/15/2012	0.19				6/15/2012	0.19	0.19	0.21
S001	6/14/2012	0.19				6/14/2012	0.19	0.2	0.21
S001	6/13/2012	0.2				6/13/2012	0.2	0.2	0.21
S001	6/12/2012	0.2				6/12/2012	0.2	0.2	0.24
S001	6/11/2012	0.21				6/11/2012	0.21	0.2	0.25
S001	6/8/2012	0.21				6/8/2012	0.21	0.19	0.25
S001	6/7/2012	0.2				6/7/2012	0.2	0.21	0.25
S001	6/6/2012	0.19				6/6/2012	0.19	0.21	0.24
S001	6/5/2012	0.19				6/5/2012	0.19	0.19	0.24
S001	6/1/2012	0.19				6/1/2012	0.19	0.2	0.24
S002	6/15/2012	0.19
S002	6/14/2012	0.2
S002	6/13/2012	0.2
S002	6/12/2012	0.2
S002	6/11/2012	0.2
S002	6/8/2012	0.19
S002	6/7/2012	0.21
S002	6/6/2012	0.21
S002	6/5/2012	0.19
S002	6/1/2012	0.2
S003	6/15/2012	0.21
S003	6/14/2012	0.21
S003	6/13/2012	0.21
S003	6/12/2012	0.24
S003	6/11/2012	0.25
S003	6/8/2012	0.25
S003	6/7/2012	0.25
S003	6/6/2012	0.24
S003	6/5/2012	0.24
S003	6/1/2012	0.24

[Haikuo · Accepted solution]

Classic transpose scenario:

data have;

input STOCK $    DATE :mmddyy10.    RETURN;

format date mmddyy10.;

cards;   

S001    6/15/2012    0.19

S001    6/14/2012    0.19

S001    6/13/2012    0.2   

S001    6/12/2012    0.2   

S001    6/11/2012    0.21

S001    6/8/2012    0.21

S001    6/7/2012    0.2   

S001    6/6/2012    0.19

S001    6/5/2012    0.19

S001    6/1/2012    0.19

S002    6/15/2012    0.19

S002    6/14/2012    0.2   

S002    6/13/2012    0.2   

S002    6/12/2012    0.2   

S002    6/11/2012    0.2   

S002    6/8/2012    0.19

S002    6/7/2012    0.21

S002    6/6/2012    0.21

S002    6/5/2012    0.19

S002    6/1/2012    0.2   

S003    6/15/2012    0.21

S003    6/14/2012    0.21

S003    6/13/2012    0.21

S003    6/12/2012    0.24

S003    6/11/2012    0.25

S003    6/8/2012    0.25

S003    6/7/2012    0.25

S003    6/6/2012    0.24

S003    6/5/2012    0.24

S003    6/1/2012    0.24

;

proc sort data=have;

by date;

run;

proc transpose data=have out=want(drop= _:) prefix=return_;

var return;

by date;

id stock;

run;

proc print;run;   

Regards,

Haikuo

[Ksharp]

How about:

data have;
infile datalines expandtabs truncover dlm=' ';
input STOCK     $ DATE : mmddyy12. RETURN     ;
format DATE  mmddyy10.;
datalines;
S001     6/15/2012     0.19     
S001     6/14/2012     0.19     
S001     6/13/2012     0.2          
S001     6/12/2012     0.2          
S001     6/11/2012     0.21     
S001     6/8/2012     0.21     
S001     6/7/2012     0.2          
S001     6/6/2012     0.19     
S001     6/5/2012     0.19     
S001     6/1/2012     0.19     
S002     6/15/2012     0.19     
S002     6/14/2012     0.2          
S002     6/13/2012     0.2          
S002     6/12/2012     0.2     
S002     6/11/2012     0.2     
S002     6/8/2012     0.19     
S002     6/7/2012     0.21     
S002     6/6/2012     0.21     
S002     6/5/2012     0.19     
S002     6/1/2012     0.2          
S003     6/15/2012     0.21     
S003     6/14/2012     0.21     
S003     6/13/2012     0.21     
S003     6/12/2012     0.24     
S003     6/11/2012     0.25     
S003     6/8/2012     0.25     
S003     6/7/2012     0.25     
S003     6/6/2012     0.24     
S003     6/5/2012     0.24
S003     6/1/2012     0.24
;
run;
proc sql noprint;
select distinct cats('have(rename=(return=return_',stock,') where=(stock="',stock,'"))') into : list separated by ' '
 from have ;
quit;
proc sort data=have;by date;run;
data want(drop=stock);
 merge &list ;
 by date;
run;

Ksharp

[Haikuo]

May I say 'Wow'!  神鬼莫测，石破天惊。

Thanks for sharing!

Haikuo

[Ksharp]

Bian Hai Kuo,

Thanks. You still don't go to sleep? It is too late in U.S.

[Linlin]

using array:

data have;

infile datalines expandtabs truncover dlm=' ';

input STOCK     $ DATE : mmddyy12. RETURN     ;

format DATE  mmddyy10.;

datalines;

S001     6/15/2012     0.19    

S001     6/14/2012     0.19    

S001     6/13/2012     0.2         

S001     6/12/2012     0.2         

S001     6/11/2012     0.21    

S001     6/8/2012     0.21    

S001     6/7/2012     0.2         

S001     6/6/2012     0.19    

S001     6/5/2012     0.19    

S001     6/1/2012     0.19    

S002     6/15/2012     0.19    

S002     6/14/2012     0.2         

S002     6/13/2012     0.2         

S002     6/12/2012     0.2    

S002     6/11/2012     0.2    

S002     6/8/2012     0.19    

S002     6/7/2012     0.21    

S002     6/6/2012     0.21    

S002     6/5/2012     0.19    

S002     6/1/2012     0.2         

S003     6/15/2012     0.21    

S003     6/14/2012     0.21    

S003     6/13/2012     0.21    

S003     6/12/2012     0.24    

S003     6/11/2012     0.25    

S003     6/8/2012     0.25    

S003     6/7/2012     0.25    

S003     6/6/2012     0.24    

S003     6/5/2012     0.24

S003     6/1/2012     0.24

;

run;

proc sort data=have;

  by date stock;

run;

%let m=3; /* m is the number of stock */

%let n=%sysfunc(putn(&m,z3.));

data want_yulin(drop=stock return count);

  set have;

    by date;

  retain return_s001-return_s&n.;

  array _r(*) return_s001-return_s&n.;

  if first.date then count=0;

  count+1;

  _r(count)=return;

  if last.date then do;

    output;

  call missing(of _r(*));

  end;

  run;

  proc print;run;

Linlin