Help using Base SAS procedures

Create long data in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Create long data in SAS

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

STOCKDATESTOCKDATE
S0016/15/2012S0016/15/2012
S0016/14/2012S0016/14/2012
S0016/13/2012S0016/13/2012
S0016/12/2012S0016/12/2012
S0016/11/2012S0016/11/2012
S0016/8/2012S0016/8/2012
S0016/7/2012S0016/7/2012
S0016/6/2012S0016/6/2012
S0016/5/2012S0016/5/2012
S0016/1/2012S0016/1/2012
S0026/15/2012
S0026/14/2012
S0026/13/2012
S0026/12/2012
S0026/11/2012
S0026/8/2012
S0026/7/2012
S0026/6/2012
S0026/5/2012
S0026/1/2012
S0036/15/2012
S0036/14/2012
S0036/13/2012
S0036/12/2012
S0036/11/2012
S0036/8/2012
S0036/7/2012
S0036/6/2012
S0036/5/2012
S0036/1/2012

Accepted Solutions
Solution
‎06-29-2012 04:37 PM
Respected Advisor
Posts: 3,156

Re: Create long data in SAS

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,920

Re: Create long data in SAS

data B;

set A;

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

output;

end;

run;

PG

PG
Super Contributor
Posts: 1,636

Re: Create long data in SAS

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;

Respected Advisor
Posts: 4,920

Re: Create long data in SAS

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

PG
Solution
‎06-29-2012 04:37 PM
Respected Advisor
Posts: 3,156

Re: Create long data in SAS

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

Occasional Contributor
Posts: 6

Re: Create long data in SAS

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

STOCKDATERETURNSTOCKDATESTOCKDATERETURN
S0016/21/2012S0016/21/2012
S0016/22/2012S0016/22/2012
S0016/23/2012S0016/23/2012
S0016/24/2012S0016/24/2012
S0016/25/2012S0016/25/2012
S0016/26/2012S0016/26/2012
S0016/27/20120.19S0016/27/2012S0016/27/20120.19
S0016/28/20120.19S0016/28/2012S0016/28/20120.19
S0016/29/20120.2S0016/29/2012S0016/29/20120.2
S0016/30/20120.2S0016/30/2012S0016/30/20120.2
S0017/1/20120.21S0017/1/2012S0017/1/20120.21
S0017/2/20120.21S0017/2/2012S0017/2/20120.21
S0017/3/20120.2S0017/3/2012S0017/3/20120.2
S0017/5/20120.19S0017/4/2012S0017/4/2012
S0017/6/20120.19S0017/5/2012S0017/5/20120.19
S0017/7/20120.19S0017/6/2012S0017/6/20120.19
S0017/8/20120.2S0017/7/2012S0017/7/20120.19
S0017/9/20120.2S0017/8/2012S0017/8/20120.2
S0017/10/20120.21S0017/9/2012S0017/9/20120.2
S0017/11/20120.21S0017/10/2012S0017/10/20120.21
S0017/15/20120.19S0017/11/2012S0017/11/20120.21
S0017/16/20120.2S0017/12/2012S0017/12/2012
S0017/17/20120.21S0017/13/2012S0017/13/2012
S0017/18/20120.21S0017/14/2012S0017/14/2012
S0017/19/20120.21S0017/15/2012S0017/15/20120.19
S0017/20/20120.24S0017/16/2012S0017/16/20120.2
S0017/21/20120.25S0017/17/2012S0017/17/20120.21
S0026/27/20120.19S0017/18/2012S0017/18/20120.21
S0026/28/20120.19S0017/19/2012S0017/19/20120.21
S0026/29/20120.2S0017/20/2012S0017/20/20120.24
S0026/30/20120.2S0017/21/2012S0017/21/20120.25
S0027/1/20120.21S0017/22/2012S0017/22/2012
S0027/2/20120.21S0017/23/2012S0017/23/2012
S0027/3/20120.2S0026/21/2012S0026/21/2012
S0027/4/20120.19S0026/22/2012S0026/22/2012
S0027/5/20120.19S0026/23/2012S0026/23/2012
S0027/6/20120.19S0026/24/2012S0026/24/2012
S0027/7/20120.19S0026/25/2012S0026/25/2012
S0027/8/20120.2S0026/26/2012S0026/26/2012
S0027/9/20120.2S0026/27/2012S0026/27/20120.19
S0027/10/20120.21S0026/28/2012S0026/28/20120.19
S0027/11/20120.21S0026/29/2012S0026/29/20120.2
S0027/15/20120.19S0026/30/2012S0026/30/20120.2
S0027/16/20120.2S0027/1/2012S0027/1/20120.21
S0027/17/20120.21S0027/2/2012S0027/2/20120.21
S0027/18/20120.21S0027/3/2012S0027/3/20120.2
S0027/19/20120.21S0027/4/2012S0027/4/20120.19
S0027/20/20120.24S0027/5/2012S0027/5/20120.19
S0027/21/20120.25S0027/6/2012S0027/6/20120.19
S0027/7/2012S0027/7/20120.19
S0027/8/2012S0027/8/20120.2
S0027/9/2012S0027/9/20120.2
S0027/10/2012S0027/10/20120.21
S0027/11/2012S0027/11/20120.21
S0027/12/2012S0027/12/2012
S0027/13/2012S0027/13/2012
S0027/14/2012S0027/14/2012
S0027/15/2012S0027/15/20120.19
S0027/16/2012S0027/16/20120.2
S0027/17/2012S0027/17/20120.21
S0027/18/2012S0027/18/20120.21
S0027/19/2012S0027/19/20120.21
S0027/20/2012S0027/20/20120.24
S0027/21/2012S0027/21/20120.25
S0027/22/2012S0027/22/2012
S0027/23/2012S0027/23/2012
Respected Advisor
Posts: 4,173

Re: Create long data in SAS

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 225 views
  • 6 likes
  • 5 in conversation