topic Re: Create long data in SAS in SAS Procedures

Create long data in SAS

onhoaian — Fri, 29 Jun 2012 17:39:08 GMT

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

Re: Create long data in SAS

PGStats — Fri, 29 Jun 2012 17:50:20 GMT

data B;

set A;

do stock = "S001", "S002", "S003";

output;

end;

run;

Re: Create long data in SAS

Linlin — Fri, 29 Jun 2012 19:00:42 GMT

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;

Re: Create long data in SAS

PGStats — Fri, 29 Jun 2012 19:26:14 GMT

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;

Re: Create long data in SAS

Haikuo — Fri, 29 Jun 2012 20:37:06 GMT

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;

stop;

drop _:;

run;

proc print;run;

Adjust the value of macro variable n to suit your needs.

Haikuo

Re: Create long data in SAS

onhoaian — Sat, 30 Jun 2012 06:41:09 GMT

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

Re: Create long data in SAS

Patrick — Sat, 30 Jun 2012 10:52:45 GMT

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".