Using date window counters

[vassilisef]

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
;
run;

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,
Vassilis

 

[vassilisef]

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

CODE:

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



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 ;
				exday=temp{2};
		end;
			do i=1 to 3 ;
					newdate=temp{i} ;
					output ;
				end;
	format temp1-temp3 mmddyy10. newdate mmddyy10. exday mmddyy10. ;
run;


data vertical_new ;
merge Split1_new split2 ;
run;

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 ??

THANK YOU ALL,
Vassilis

 

[ChrisW75]

Couldn't you just do this.

data new;
  set old;

  zero_date = date +(-1*counter);
run;

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

 

[vassilisef]

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,
Vassilis

 

[ChrisW75]

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 = .;
run;

proc sort data=vertical2;
  by descending recID;
run;

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);
run;

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.

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

 

[vassilisef]

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,
Vassilis