Help using Base SAS procedures

How can I expand data by time? Thanks

Reply
New Contributor
Posts: 4

How can I expand data by time? Thanks

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.

Respected Advisor
Posts: 4,173

Re: How can I expand data by time? Thanks

Posted in reply to thinksgiving

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

New Contributor
Posts: 4

Re: How can I expand data by time? Thanks

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

Respected Advisor
Posts: 4,173

Re: How can I expand data by time? Thanks

Posted in reply to thinksgiving

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?

New Contributor
Posts: 4

Re: How can I expand data by time? Thanks

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.

Respected Advisor
Posts: 4,173

Re: How can I expand data by time? Thanks

Posted in reply to thinksgiving

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=_Smiley Happy;

  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.

New Contributor
Posts: 4

Re: How can I expand data by time? Thanks

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

Respected Advisor
Posts: 4,173

Re: How can I expand data by time? Thanks

Posted in reply to thinksgiving

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. 

Respected Advisor
Posts: 3,156

Re: How can I expand data by time? Thanks

Posted in reply to thinksgiving

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

Ask a Question
Discussion stats
  • 8 replies
  • 304 views
  • 0 likes
  • 3 in conversation