topic Re: Long data to wide data in SAS Procedures

Long data to wide data

onhoaian — Wed, 04 Jul 2012 02:56:41 GMT

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

Re: Long data to wide data

Haikuo — Wed, 04 Jul 2012 03:21:58 GMT

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

Re: Long data to wide data

Ksharp — Wed, 04 Jul 2012 03:27:02 GMT

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

Re: Long data to wide data

Haikuo — Wed, 04 Jul 2012 03:50:37 GMT

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

Thanks for sharing!

Haikuo

Re: Long data to wide data

Ksharp — Wed, 04 Jul 2012 04:35:36 GMT

Bian Hai Kuo,

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

Re: Long data to wide data

Linlin — Wed, 04 Jul 2012 21:27:05 GMT

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