Help using Base SAS procedures

Long data to wide data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Long data to wide data

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.

STOCKDATERETURNDATERETURN_S001RETURN_S002RETURN_S003
S0016/15/20120.196/15/20120.190.190.21
S0016/14/20120.196/14/20120.190.20.21
S0016/13/20120.26/13/20120.20.20.21
S0016/12/20120.26/12/20120.20.20.24
S0016/11/20120.216/11/20120.210.20.25
S0016/8/20120.216/8/20120.210.190.25
S0016/7/20120.26/7/20120.20.210.25
S0016/6/20120.196/6/20120.190.210.24
S0016/5/20120.196/5/20120.190.190.24
S0016/1/20120.196/1/20120.190.20.24
S0026/15/20120.19
S0026/14/20120.2
S0026/13/20120.2
S0026/12/20120.2
S0026/11/20120.2
S0026/8/20120.19
S0026/7/20120.21
S0026/6/20120.21
S0026/5/20120.19
S0026/1/20120.2
S0036/15/20120.21
S0036/14/20120.21
S0036/13/20120.21
S0036/12/20120.24
S0036/11/20120.25
S0036/8/20120.25
S0036/7/20120.25
S0036/6/20120.24
S0036/5/20120.24
S0036/1/20120.24

Accepted Solutions
Solution
‎07-03-2012 11:21 PM
Respected Advisor
Posts: 3,124

Re: Long data to wide data

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= _Smiley Happy prefix=return_;

var return;

by date;

id stock;

run;

proc print;run;   

Regards,

Haikuo

View solution in original post


All Replies
Solution
‎07-03-2012 11:21 PM
Respected Advisor
Posts: 3,124

Re: Long data to wide data

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= _Smiley Happy prefix=return_;

var return;

by date;

id stock;

run;

proc print;run;   

Regards,

Haikuo

Super User
Posts: 9,687

Re: Long data to wide data

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

Respected Advisor
Posts: 3,124

Re: Long data to wide data

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

Thanks for sharing!

Haikuo

Super User
Posts: 9,687

Re: Long data to wide data

Bian Hai Kuo,

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

Super Contributor
Posts: 1,636

Re: Long data to wide data

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 239 views
  • 3 likes
  • 4 in conversation