DATA Step, Macro, Functions and more

Reorder time series data

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Reorder time series data

I currently have a dataset that has observations sorted by times going across the top.    The observations being at different time periods.

project   q1 2008  q2 2008 q32008 q42008 q12009 q2009 q32009 q42009  ......

1              4             5          8           8           9       12      14         16

2             0              0          3           6           5        7        10           8

3             4              3          5           4          3         5        0            3

4             0              0          0           0           5        4         7           10

5             0              0          0           0           0        0         3            2

I would like to rearrange the data so that every observation beings a the same time.  Ideally the dataset

would like this:

project       t1            t2            t3         t4        t5         t6         t7      t8

1               4             5             8          8          9         12       14      16

2               3             6             5          7         10        8           .        .

3               4             3             5         4          3          5         0         3

4               5             4             7        10          .           .         .           .

5               3             2              .           .          .          .          .         .

Any suggestions would be greatly appreciated. 

Thanks


Accepted Solutions
Solution
‎09-29-2011 09:33 PM
PROC Star
Posts: 7,492

Re: Reorder time series data

I'm not really sure what you want to do and, besides, I don't have a way tonight to test the following code. However, that said, I think that the following accomplishes what you showed in your example:

data have;

  input project   q12008  q22008 q32008 q42008 q12009 q2009 q32009 q42009;

  cards;

1              4             5          8           8           9       12      14         16

2             0              0          3           6           5        7        10           8

3             4              3          5           4          3         5        0            3

4             0              0          0           0           5        4         7           10

5             0              0          0           0           0        0         3            2

;

data want;

  set have;

  array projects(*) q12008--q42009;

  do i=1 to dim(projects);

    if projects(i) gt 0 then do;

      found=i;

      i=dim(projects)+1;

  end;

  j=0;

  do i=found to dim(projects);

    j+1;

    projects(j)=projects(i);

  end;

  do i=dim(projects)-found+2 to dim(projects);

    call missing(projects(i);

  end;

run;

View solution in original post


All Replies
Solution
‎09-29-2011 09:33 PM
PROC Star
Posts: 7,492

Re: Reorder time series data

I'm not really sure what you want to do and, besides, I don't have a way tonight to test the following code. However, that said, I think that the following accomplishes what you showed in your example:

data have;

  input project   q12008  q22008 q32008 q42008 q12009 q2009 q32009 q42009;

  cards;

1              4             5          8           8           9       12      14         16

2             0              0          3           6           5        7        10           8

3             4              3          5           4          3         5        0            3

4             0              0          0           0           5        4         7           10

5             0              0          0           0           0        0         3            2

;

data want;

  set have;

  array projects(*) q12008--q42009;

  do i=1 to dim(projects);

    if projects(i) gt 0 then do;

      found=i;

      i=dim(projects)+1;

  end;

  j=0;

  do i=found to dim(projects);

    j+1;

    projects(j)=projects(i);

  end;

  do i=dim(projects)-found+2 to dim(projects);

    call missing(projects(i);

  end;

run;

New Contributor
Posts: 2

Reorder time series data

Thanks! this is exactly what I was looking for. This works perfectly.

I made only two tiny changes to your code:

art297 wrote:

data want;

  set have;

  array projects(*) q12008--q42009;

  do i=1 to dim(projects);

    if projects(i) gt 0 then do;

      found=i;

      i=dim(projects)+1;

  end;

  j=0;

  end;

  do i=found to dim(projects);

    j+1;

    projects(j)=projects(i);

  end;

  do i=dim(projects)-found+2 to dim(projects);

    call missing(projects(i));

  end;

run;

Respected Advisor
Posts: 3,799

Re: Reorder time series data

You can use PROC TRANSPOSE for a more concise solution.  If your data had extra numeric variables you would need to include a VAR Q:; statment in the first PROC TRANSPOSE.  

EDIT: Art brought it to my attention that my program did not meet the specs.  This corrected version seems to provide the correct result. Not so concise.  But still much simpler that shifting value in arrays.

data have;
  input project q12008 q22008 q32008 q42008 q12009 q2009 q32009 q42009;
  cards;
1 4 5 8 8 9 12 14 16
2 0 0 3 6 5  7 10  8
3 4 3 5 4 3  5  0  3
4 0 0 0 0 5  4  7 10
5 0 0 0 0 0  0  3  2
;;;;
proc transpose out=tall;
   by project;
   run;
data tallV / view=tallV;
   set tall;
   by project col1 notsorted;
   if first.project and col1 eq 0 then _iorc_=1;
   if _iorc_ and col1 eq 0 then do;
      if last.col1 then _iorc_ + -1;
      delete;
      end;
   run;
proc transpose prefix=T out=T(drop=_name_);
   by project;
   run;
proc print;
   run;

Obs    project    T1    T2    T3    T4    T5    T6    T7    T8

1        1        4     5     8     8     9    12    14    16

2        2        3     6     5     7    10     8     .     .

3        3        4     3     5     4     3     5     0     3

4        4        5     4     7    10     .     .     .     .

5        5        3     2     .     .     .     .     .     .


PROC Star
Posts: 7,492

Re: Reorder time series data

Posted in reply to data_null__

Datanull,  According to the OP's specs 0s that came after a non-zero value had to be kept.

Respected Advisor
Posts: 3,799

Re: Reorder time series data

Art,

Here is a method using maximum obfuscation. :smileymischief:  It is the most direct way I could come up with to "slide" variable values, in this case to the left.  As long as dim(q)*8 is less than 32767 this should work.

data have;

   input project q12008 q22008 q32008 q42008 q12009 q2009 q32009 q42009;

   array q

  • q:;
  •    do _n_ = 1 to dim(q) by 1 until(q[_n_] gt 0); end;

       if _n_ eq 1 then return;

       _iorc_   = (dim(q)-_n_+1)*8;

       _infile_ = peekClong(addrlong(q[_n_]),_iorc_);

       call missing(of q

  • );
  •    call pokelong(_infile_,addrlong(q[1]),_iorc_);

       cards;

    1 4 5 8 8 9 12 14 16

    2 0 0 3 6 5  7 10  8

    3 4 3 5 4 3  5  0  3

    4 0 0 0 0 5  4  7 10

    5 0 0 0 0 0  0  3  2

    ;;;;

    proc print;

       run;

    Message was edited by: data _null_ corrected spelling.

    PROC Star
    Posts: 7,492

    Re: Reorder time series data

    Posted in reply to data_null__

    Datanull,  Art C would be proud(?) of your efforts (see, e.g., http://www.caloxy.com/papers/63_TT05.pdf )

    Valued Guide
    Posts: 2,177

    Reorder time series data

    Posted in reply to data_null__

    that looks like one from PaulDorfman although better known as hashman, he is a great proponent of  memory functions for performance

    Respected Advisor
    Posts: 3,799

    Reorder time series data

    Yes, I was thinking that code was somewhat Dorfmanesk especially the use of _N_, _IORC_ and _INFILE_  for purposes for which they might not otherwise be used. Everything in that code was learned from Mr. Dorfman.

    Super User
    Posts: 10,046

    Re: Reorder time series data

    Posted in reply to data_null__

    Null. I am big surprised that the order of data will not be changed after proc transpose and deleting the first several zero.But still not suited for large dataset.

    Ksharp

    Respected Advisor
    Posts: 3,799

    Re: Reorder time series data

    Ksharp wrote:

    Null. I am big surprised that the order of data will not be changed after proc transpose and deleting the first several zero.But still not suited for large dataset.

    Ksharp


    I don't know why you are surprised that the order of the data would not change.  Nothing is done to change it.  What did you think would happen?

    I expect it would be fast enough most of the time.

    Using PROC TRANSPOSE makes this what I like to call a "SAS Program".  A program that uses SAS features not general coding, arrays, subscripts, counters and the rest.  Like the difference between using PROC FREQ vs programming counts in a data step.  Both produce the same result PROC FREQ being easier to code.

    Super User
    Posts: 10,046

    Re: Reorder time series data

    Posted in reply to data_null__

    Null.

    It is apparent that I learned somthing new from you again. Thx.

    Ksharp

    Super User
    Posts: 10,046

    Re: Reorder time series data

    I know art.t has already given an answer.

    But I just want to try it. Forgive my curiosity. Smiley Happy

     
    
    data have;
      input project   q12008  q22008 q32008 q42008 q12009 q2009 q32009 q42009;
      cards;
    1              4             5          8           8           9       12      14         16
    2             0              0          3           6           5        7        10           8
    3             4              3          5           4          3         5        0            3
    4             0              0          0           0           5        4         7           10
    5             0              0          0           0           0        0         3            2
    ;
    run;
    
     
    
    data want(drop=i j k );
      set have;
      array projects{*} q12008--q42009;
      i=1;
      do while (projects{i} eq 0 and i le dim(projects) );
      projects{i}=.;i+1;
      end;
      if i gt 1 and i lt dim(projects) then do;
        do j=1 to dim(projects)-1;
          do k=j+1 to dim(projects);
           if missing(projects{j}) then do;
                                        projects{j}=projects{k};
                                               projects{k}=.;
                                               end;
          end;
         end;
                                               end;
    run;
    
    
    
    

    Ksharp

    消息编辑者为:xia keshan Ops. Not Notice totally. It is correct code.

    🔒 This topic is solved and locked.

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

    Discussion stats
    • 12 replies
    • 265 views
    • 0 likes
    • 5 in conversation