I would like to create many n sets of data (B) from just a single set of data (A) illustrated below using SAS. Please kindly help.
Thank you & best regards
A B
STOCK | DATE | STOCK | DATE | |
S001 | 6/15/2012 | S001 | 6/15/2012 | |
S001 | 6/14/2012 | S001 | 6/14/2012 | |
S001 | 6/13/2012 | S001 | 6/13/2012 | |
S001 | 6/12/2012 | S001 | 6/12/2012 | |
S001 | 6/11/2012 | S001 | 6/11/2012 | |
S001 | 6/8/2012 | S001 | 6/8/2012 | |
S001 | 6/7/2012 | S001 | 6/7/2012 | |
S001 | 6/6/2012 | S001 | 6/6/2012 | |
S001 | 6/5/2012 | S001 | 6/5/2012 | |
S001 | 6/1/2012 | S001 | 6/1/2012 | |
S002 | 6/15/2012 | |||
S002 | 6/14/2012 | |||
S002 | 6/13/2012 | |||
S002 | 6/12/2012 | |||
S002 | 6/11/2012 | |||
S002 | 6/8/2012 | |||
S002 | 6/7/2012 | |||
S002 | 6/6/2012 | |||
S002 | 6/5/2012 | |||
S002 | 6/1/2012 | |||
S003 | 6/15/2012 | |||
S003 | 6/14/2012 | |||
S003 | 6/13/2012 | |||
S003 | 6/12/2012 | |||
S003 | 6/11/2012 | |||
S003 | 6/8/2012 | |||
S003 | 6/7/2012 | |||
S003 | 6/6/2012 | |||
S003 | 6/5/2012 | |||
S003 | 6/1/2012 |
Hi,
if you want your 'n' more dynamic than '3', here is an approach:
data have;
input stock $ date :mmddyy10.;
format date mmddyy10.;
cards;
S001 6/15/2012
S001 6/14/2012
S001 6/13/2012
S001 6/12/2012
S001 6/11/2012
S001 6/8/2012
S001 6/7/2012
S001 6/6/2012
S001 6/5/2012
S001 6/1/2012
;
%let n=10;
data want;
do _i=1 to &n;
do _p=1 to nobs;
set have point=_p nobs=nobs;
if _n_=1 then _s=first(stock);
stock=cats(_s,put(_i,z3.));
output;
end;
end;
stop;
drop _:;
run;
proc print;run;
Adjust the value of macro variable n to suit your needs.
Haikuo
data B;
set A;
do stock = "S001", "S002", "S003";
output;
end;
run;
PG
or
data a;
informat date mmddyy10.;
format date mmddyy10.;
input stock :$4. date;
cards;
s001 06/01/2012
s001 06/02/2012
s001 06/03/2012
s001 06/04/2012
;
data a2;
set a;
stock='s002';
data a3;
set a;
stock='s003';
data b;
set a a2 a3;
run;
proc print;run;
Or, if you want to keep the original variable and observation order :
data A;
input stock :$4. date :mmddyy10.;
format date mmddyy10.;
datalines;
S001 06/01/2012
S001 06/02/2012
S001 06/03/2012
S001 06/04/2012
;
data B;
set A;
_obs = _n_;
do stock = "S001", "S002", "S003";
output;
end;
run;
proc sort data=B out=B(drop=_obs); by stock _obs; run;
proc print; run;
PG
Hi,
if you want your 'n' more dynamic than '3', here is an approach:
data have;
input stock $ date :mmddyy10.;
format date mmddyy10.;
cards;
S001 6/15/2012
S001 6/14/2012
S001 6/13/2012
S001 6/12/2012
S001 6/11/2012
S001 6/8/2012
S001 6/7/2012
S001 6/6/2012
S001 6/5/2012
S001 6/1/2012
;
%let n=10;
data want;
do _i=1 to &n;
do _p=1 to nobs;
set have point=_p nobs=nobs;
if _n_=1 then _s=first(stock);
stock=cats(_s,put(_i,z3.));
output;
end;
end;
stop;
drop _:;
run;
proc print;run;
Adjust the value of macro variable n to suit your needs.
Haikuo
Thank you for all your kind supports. May I suggest another problem?
I have data A with loss of data and data B with full trading days, after merging I get data C. For days of loss data and only in the boundaries of trading days of stock S001, S002, S00..in data A, the return in those days will equal to the returns of the previous days (lag-1). How could I do this for the data C? To be more specific, for stock S001 I would like to fill the value for loss data days of 7/4/2012 and 7/12-7/14/2012 (in red colors and to be .2 .21 .21 .21 respectively), but not for days outside the boundaries of 6/26/2012 and 7/22/2012 when the stock are not traded.
Thanks and best regards.
A B C
STOCK | DATE | RETURN | STOCK | DATE | STOCK | DATE | RETURN | ||||
S001 | 6/21/2012 | S001 | 6/21/2012 | ||||||||
S001 | 6/22/2012 | S001 | 6/22/2012 | ||||||||
S001 | 6/23/2012 | S001 | 6/23/2012 | ||||||||
S001 | 6/24/2012 | S001 | 6/24/2012 | ||||||||
S001 | 6/25/2012 | S001 | 6/25/2012 | ||||||||
S001 | 6/26/2012 | S001 | 6/26/2012 | ||||||||
S001 | 6/27/2012 | 0.19 | S001 | 6/27/2012 | S001 | 6/27/2012 | 0.19 | ||||
S001 | 6/28/2012 | 0.19 | S001 | 6/28/2012 | S001 | 6/28/2012 | 0.19 | ||||
S001 | 6/29/2012 | 0.2 | S001 | 6/29/2012 | S001 | 6/29/2012 | 0.2 | ||||
S001 | 6/30/2012 | 0.2 | S001 | 6/30/2012 | S001 | 6/30/2012 | 0.2 | ||||
S001 | 7/1/2012 | 0.21 | S001 | 7/1/2012 | S001 | 7/1/2012 | 0.21 | ||||
S001 | 7/2/2012 | 0.21 | S001 | 7/2/2012 | S001 | 7/2/2012 | 0.21 | ||||
S001 | 7/3/2012 | 0.2 | S001 | 7/3/2012 | S001 | 7/3/2012 | 0.2 | ||||
S001 | 7/5/2012 | 0.19 | S001 | 7/4/2012 | S001 | 7/4/2012 | |||||
S001 | 7/6/2012 | 0.19 | S001 | 7/5/2012 | S001 | 7/5/2012 | 0.19 | ||||
S001 | 7/7/2012 | 0.19 | S001 | 7/6/2012 | S001 | 7/6/2012 | 0.19 | ||||
S001 | 7/8/2012 | 0.2 | S001 | 7/7/2012 | S001 | 7/7/2012 | 0.19 | ||||
S001 | 7/9/2012 | 0.2 | S001 | 7/8/2012 | S001 | 7/8/2012 | 0.2 | ||||
S001 | 7/10/2012 | 0.21 | S001 | 7/9/2012 | S001 | 7/9/2012 | 0.2 | ||||
S001 | 7/11/2012 | 0.21 | S001 | 7/10/2012 | S001 | 7/10/2012 | 0.21 | ||||
S001 | 7/15/2012 | 0.19 | S001 | 7/11/2012 | S001 | 7/11/2012 | 0.21 | ||||
S001 | 7/16/2012 | 0.2 | S001 | 7/12/2012 | S001 | 7/12/2012 | |||||
S001 | 7/17/2012 | 0.21 | S001 | 7/13/2012 | S001 | 7/13/2012 | |||||
S001 | 7/18/2012 | 0.21 | S001 | 7/14/2012 | S001 | 7/14/2012 | |||||
S001 | 7/19/2012 | 0.21 | S001 | 7/15/2012 | S001 | 7/15/2012 | 0.19 | ||||
S001 | 7/20/2012 | 0.24 | S001 | 7/16/2012 | S001 | 7/16/2012 | 0.2 | ||||
S001 | 7/21/2012 | 0.25 | S001 | 7/17/2012 | S001 | 7/17/2012 | 0.21 | ||||
S002 | 6/27/2012 | 0.19 | S001 | 7/18/2012 | S001 | 7/18/2012 | 0.21 | ||||
S002 | 6/28/2012 | 0.19 | S001 | 7/19/2012 | S001 | 7/19/2012 | 0.21 | ||||
S002 | 6/29/2012 | 0.2 | S001 | 7/20/2012 | S001 | 7/20/2012 | 0.24 | ||||
S002 | 6/30/2012 | 0.2 | S001 | 7/21/2012 | S001 | 7/21/2012 | 0.25 | ||||
S002 | 7/1/2012 | 0.21 | S001 | 7/22/2012 | S001 | 7/22/2012 | |||||
S002 | 7/2/2012 | 0.21 | S001 | 7/23/2012 | S001 | 7/23/2012 | |||||
S002 | 7/3/2012 | 0.2 | S002 | 6/21/2012 | S002 | 6/21/2012 | |||||
S002 | 7/4/2012 | 0.19 | S002 | 6/22/2012 | S002 | 6/22/2012 | |||||
S002 | 7/5/2012 | 0.19 | S002 | 6/23/2012 | S002 | 6/23/2012 | |||||
S002 | 7/6/2012 | 0.19 | S002 | 6/24/2012 | S002 | 6/24/2012 | |||||
S002 | 7/7/2012 | 0.19 | S002 | 6/25/2012 | S002 | 6/25/2012 | |||||
S002 | 7/8/2012 | 0.2 | S002 | 6/26/2012 | S002 | 6/26/2012 | |||||
S002 | 7/9/2012 | 0.2 | S002 | 6/27/2012 | S002 | 6/27/2012 | 0.19 | ||||
S002 | 7/10/2012 | 0.21 | S002 | 6/28/2012 | S002 | 6/28/2012 | 0.19 | ||||
S002 | 7/11/2012 | 0.21 | S002 | 6/29/2012 | S002 | 6/29/2012 | 0.2 | ||||
S002 | 7/15/2012 | 0.19 | S002 | 6/30/2012 | S002 | 6/30/2012 | 0.2 | ||||
S002 | 7/16/2012 | 0.2 | S002 | 7/1/2012 | S002 | 7/1/2012 | 0.21 | ||||
S002 | 7/17/2012 | 0.21 | S002 | 7/2/2012 | S002 | 7/2/2012 | 0.21 | ||||
S002 | 7/18/2012 | 0.21 | S002 | 7/3/2012 | S002 | 7/3/2012 | 0.2 | ||||
S002 | 7/19/2012 | 0.21 | S002 | 7/4/2012 | S002 | 7/4/2012 | 0.19 | ||||
S002 | 7/20/2012 | 0.24 | S002 | 7/5/2012 | S002 | 7/5/2012 | 0.19 | ||||
S002 | 7/21/2012 | 0.25 | S002 | 7/6/2012 | S002 | 7/6/2012 | 0.19 | ||||
S002 | 7/7/2012 | S002 | 7/7/2012 | 0.19 | |||||||
S002 | 7/8/2012 | S002 | 7/8/2012 | 0.2 | |||||||
S002 | 7/9/2012 | S002 | 7/9/2012 | 0.2 | |||||||
S002 | 7/10/2012 | S002 | 7/10/2012 | 0.21 | |||||||
S002 | 7/11/2012 | S002 | 7/11/2012 | 0.21 | |||||||
S002 | 7/12/2012 | S002 | 7/12/2012 | ||||||||
S002 | 7/13/2012 | S002 | 7/13/2012 | ||||||||
S002 | 7/14/2012 | S002 | 7/14/2012 | ||||||||
S002 | 7/15/2012 | S002 | 7/15/2012 | 0.19 | |||||||
S002 | 7/16/2012 | S002 | 7/16/2012 | 0.2 | |||||||
S002 | 7/17/2012 | S002 | 7/17/2012 | 0.21 | |||||||
S002 | 7/18/2012 | S002 | 7/18/2012 | 0.21 | |||||||
S002 | 7/19/2012 | S002 | 7/19/2012 | 0.21 | |||||||
S002 | 7/20/2012 | S002 | 7/20/2012 | 0.24 | |||||||
S002 | 7/21/2012 | S002 | 7/21/2012 | 0.25 | |||||||
S002 | 7/22/2012 | S002 | 7/22/2012 | ||||||||
S002 | 7/23/2012 | S002 | 7/23/2012 |
Suggest you start a new thread with a new problem - and mark this one as answered also giving the ones helping you the correct/helpful "marks".
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.