BookmarkSubscribeRSS Feed
thinksgiving
Calcite | Level 5

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.

8 REPLIES 8
Patrick
Opal | Level 21

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

thinksgiving
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 930 views
  • 0 likes
  • 3 in conversation