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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.