Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using date window counters

Using date window counters

Using date window counters

Hey all,

I am a non-expert, learning SAS for the past 10 months.
I am having one data set with 3 constant counter {-1, +1) windows of dates for one name (ABC):


data vertical ;
input @1 name $3. +1 counter 2. +1 date mmddyy10. +1 price 3.1 ;
format date mmddyy10. ;
datalines ;
ABC -1 12/23/2008 1.5
ABC 0  12/24/2008 .
ABC 1  12/25/2008 2.1
ABC -1 12/23/2007 2.6
ABC 0  12/24/2007 .
ABC 1  12/25/2007 .
ABC -1 12/23/2006 2.5
ABC 0  12/24/2006 .
ABC 1  12/25/2006 2.4

and I would like to add a new column that repeats the date referring to the "0" counter value for each constant counter {-1, +1) window so that it looks like:

ABC -1 12/23/2008 1.5 12/24/2008
ABC 0  12/24/2008 .    12/24/2008
ABC 1  12/25/2008 2.1 12/24/2008
ABC -1 12/23/2007 2.6 12/24/2007
ABC 0  12/24/2007 .    12/24/2007
ABC 1  12/25/2007 .    12/24/2007
ABC -1 12/23/2006 2.5 12/24/2006
ABC 0  12/24/2006 .    12/24/2006
ABC 1  12/25/2006 2.4 12/24/2006

Please note that in my actual data set my constant counter window is much longer (-300, +300} and I have multiple names-identifiers ( > 10,000). Therefore, transposing any variable by counter window would be preferably avoided.

Thank you in advance,


RE: Using date window counters

Hey all,

I think that i have worked it out, but I would like to ask your view on how safe my solution is ?

I proceed in 3 steps :

1) I split my data into two parts: One data set containing just the name and date variables and another containing the name and all the other variables (counter and price).

2) Using a temporary array on the first part (name & date), I create the extra column that i want.

3) Using a MERGE without a BY Statement, I add all the columns back together



data split1 (keep=name date) split2 (keep=name counter price) ;
set vertical ;

data split1_new (drop=temp1-temp3 date i )  ;
length name $3 ;
array temp{3} temp1-temp3 ;
    do i=1 to 3 ;
        set split1  ;
                 temp{i}=date ;
            do i=1 to 3 ;
                    newdate=temp{i} ;
                    output ;
    format temp1-temp3 mmddyy10. newdate mmddyy10. exday mmddyy10. ;

data vertical_new ;
merge Split1_new split2 ;

Given that the counter window {-1, +1} remains fixed, I believe that the error risk is minimum, right ?

Is there a way to double check that no misplacement or error has occurred ??


RE: Using date window counters

Couldn't you just do this.


data new;
  set old;

  zero_date = date +(-1*counter);


Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

RE: Using date window counters

Hey Chris,

thank you very much for your reply.

You are - as always - right, since your "basic" code would do the job in the data sample that i have provided above.

However, I haven' t mentioned that in my actual set, I have stock trading trades rather calendar dates. Therefore, they don't change by 1 , as weekends & nontrading dates are omitted.

Thanks again,

RE: Using date window counters

Fair enough.
You know, if you had another variable on the dataset which linked the groups (ie was common for the first 3 values, then for the second 3 etc), the answer to this would be trivial.
I'll admit I'm having trouble following what your code is doing, I'm not big on arrays and creative uses of the set statement, however I think I can see a different method.


data vertical2;
  set vertical;

  recid = _N_;
  retain fill_down;
  if counter = 0 then fill_down = date;
  if counter < 0 then fill_down = .;

proc sort data=vertical2;
  by descending recID;

data vertical2;
  set vertical;

  retain fill_up;
  if counter = 0 then fill_up = date;
  if counter > 0 then fill_up = .;

  zero_Date = coalesce(fill_up,fill_down);

I think that this should do it as well, but only if the records are ordered as you've presented them above, which I think that they'd have to be.

Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

RE: Using date window counters

Hey Chris,

I don't have any variable with same values within each counter group. That is why I am creating one such with this code.

The logic of my Code is the following:

In the 2nd step, first, I am creating a temporary array which "reads" all the dates within each counter group. Then I extract the counter "0" date in a separate variable column "exday". Finally, I am returning the temporary array date values back into their original order.

In effect, I am doing a horizontal & vertical transposition of each counter date window, simultaneously.

However, your proposed solution - as expected - is much simpler, safer and efficient given that my counter windows are 600-values long [-300, +300] so that the transposition that I am suggesting would need substantial memory resources.

Thank you very much for you time spent on my query,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close