How can I expand data by time? Thanks

[thinksgiving]

I have a data with date variable time1, time2, time3, .....,timeN. At each time point there may be more than one observation. I hope to expand the data according to time. The first subset contains the data from time1 to time2, the second subset contains the data from time1 to time3, the third contains the data from time1 to time4, and so on, until N. And please add a new variable to identify each created subset. Best Wishes.

[Patrick]

Please provide a data step creating sample data and then show us how the expected result should look like.

[thinksgiving]

Thanks for your reply ^_^

The original data is similar to this:

Obs    pirce     date                         

1          11       2013/01/01                         

2          12       2013/02/05                          

3          10       2013/02/05     

4          10       2013/02/14      

5          11       2013/03/10

The expected data is as follows:

Obs    pirce     date                      group                        

1          11       2013/01/01                1

2          11       2013/01/01                2         

3          12       2013/02/05                2

4          10       2013/02/05                2

5          11       2013/01/01                3         

6          12       2013/02/05                3

7          10       2013/02/05                3

8          10       2013/02/14                3

9          11       2013/01/01                4         

10        12       2013/02/05                4

11        10       2013/02/05                4

12        10       2013/02/14                4

13        11       2013/03/10                4

[Patrick]

I can think of approaches using either a "dow-loop" http://support.sas.com/resources/papers/proceedings14/1619-2014.pdf or a SAS Hash table.

What have you done so far? How does your current code look like and where did you get stuck?

[thinksgiving]

Dear Patrick,

        Thanks for your suggestion. I will read the suggestions you provide carefully and try to find someting useful for the problem.

        For the problem I raise I think probably I can add something to one rolling window program to get the result. The prorgam I use is as follows (I get from other people):

Expand the data.

             data expanded(drop = nn);

             length span $ 13;

             set demo;
             do nn = 1 to &nqtrs;

             span = catx(   '-'

                             , put(intnx('quarter',date,nn-&nqtrs),yyq6.)

                             , put(intnx('quarter',date,nn-1     ),yyq6.)

                           );

                output;

                 end;

               run;

               I am wondering whether is any classical progress for this issue. So I post up the question.

               I am very grateful for the materials you provide. I will take good use of it.

               Best Wishes.

[Patrick]

One way to go shown in code below:

data have;

  input price date:yymmdd.;

  format date date9.;

  datalines;

11 2013/01/01

12 2013/02/05

10 2013/02/05

10 2013/02/14

11 2013/03/10

;

run;

proc sort data=have;

  by date;

run;

data want(drop=_:);

  set have(keep=date rename=(date=_date));

  by _date;

  if _n_=1 then

    do;

      if 0 then set have(keep=date price);

      dcl hash h1(dataset:'have(keep=date price)', multidata:'y', ordered:'y');

      _rc=h1.defineKey('date');

      _rc=h1.defineData(all:'y');

      _rc=h1.defineDone();

      dcl hiter iter1('h1');

    end;

  if last._date then

    do;

      _rc = iter1.first();

      group+1;

      do while (_rc = 0);

        if _date>=date then output want;

        else leave;

        _rc = iter1.next();

      end;   

    end;

run;

Message was edited by: Patrick Matter Removed intermediary data step as not required.

[thinksgiving]

Dear Patrick Matter,

        I do not know how to thank you. You spend time to help me although we do not know each other before. Is it possible that I buy you a dinner? Are you the manager of this website? Is it possible for me to do anything for you or do some contribution to this website?

        The program you provide is very helpful. Although at present I do not get the data I want, I believe there is something I miss. I will check it more carefulley later.

        Best Wishes.

                                 YOURS SINCERELY

[Patrick]

Mark the answer which solved your problem as "correct" and answers which where helpful as "helpful". That's one way how we thank each other in this forum.

And then just the normal rules: Spend the time to formulate a proper question and provide feedback on peoples answers. That's all what's needed here in this forum. 

[Haikuo]

While you sure can resort to Macro loops to achieve your goal (not my favorite), there are other options worth considering, such as the one offered by . Here are two more approaches:

data have;

     input price date:yymmdd.;

     format date yymmdd10.;

     datalines;

11 2013/01/01

12 2013/02/05

10 2013/02/05

10 2013/02/14

11 2013/03/10

;

run;

/*traditional way*/

data want;

     nobs=nobs;

     do group=1 to nobs;

           do i=1 to group;

                set have nobs=nobs point=i;

                output;

           end;

     end;

     stop;

run;

/*Far more efficient on I/O, Hash method, if your memory is

large enough to hold the whole incoming table*/

data want_hash;

     if 0 then

           set have;

     declare hash h(dataset: 'have', multidata: 'y', ordered: 'a');

     h.definekey('date');

     h.definedata(all:'y');

     h.definedone();

     declare hiter hi('h');

     do group=1 to h.num_items;

           rc=hi.first();

           do i=1 to group;

                output;

                rc=hi.next();

           end;

     end;

     drop i rc;

run;

Good luck,

Haikuo