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 |
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
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
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
May I say 'Wow'! 神鬼莫测,石破天惊。
Thanks for sharing!
Haikuo
Bian Hai Kuo,
Thanks. You still don't go to sleep? It is too late in U.S.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.