topic Re: How can I expand data by time? Thanks in SAS Procedures

How can I expand data by time? Thanks

thinksgiving — Sun, 07 Dec 2014 01:50:48 GMT

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.

Re: How can I expand data by time? Thanks

Patrick — Sun, 07 Dec 2014 01:52:37 GMT

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

Re: How can I expand data by time? Thanks

thinksgiving — Sun, 07 Dec 2014 02:16:31 GMT

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

Re: How can I expand data by time? Thanks

Patrick — Mon, 08 Dec 2014 00:45:04 GMT

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?

Re: How can I expand data by time? Thanks

thinksgiving — Mon, 08 Dec 2014 02:39:51 GMT

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.

Re: How can I expand data by time? Thanks

Patrick — Mon, 08 Dec 2014 04:47:39 GMT

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;

run;

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

Re: How can I expand data by time? Thanks

Haikuo — Mon, 08 Dec 2014 16:52:22 GMT

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;

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;

drop i rc;

run;

Good luck,

Haikuo

Re: How can I expand data by time? Thanks

thinksgiving — Mon, 08 Dec 2014 17:34:24 GMT

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

Re: How can I expand data by time? Thanks

Patrick — Mon, 08 Dec 2014 19:24:08 GMT

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.