BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
onhoaian
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

5 REPLIES 5
Haikuo
Onyx | Level 15

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
Super User

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
Onyx | Level 15

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

Thanks for sharing!

Haikuo

Ksharp
Super User

Bian Hai Kuo,

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

Linlin
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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