Page 1 of 1
rolllike function
Posted: Mon Jan 07, 2008 11:50 am
by arsh14
I am trying to duplicate the mainframe SAS code for Rolllike function.
I have 4 sets of columns as keys and 2 sets of dates. See data below:
K1 K2 D1 D2 F1 F2
A 1 2006-01-01 2006-12-31 X 10
A 1 2007-01-01 2007-12-31 X 10
A 1 2008-01-01 2009-12-31 X 10
A 2 2006-01-01 2006-12-31 X 10
A 2 2007-01-01 2007-12-31 Y 10
A 2 2008-01-01 2009-12-31 Z 10
The expected output is
A 1 2006-01-01 2009-12-31 X 10
A 2 2006-01-01 2006-12-31 X 10
A 2 2007-01-01 2007-12-31 Y 10
A 2 2008-01-01 2009-12-31 Z 10
The keys are K1,K2,F1 and F2. I want the min of D1 and Max of D2 for a given set of keys.
Posted: Mon Jan 07, 2008 11:54 am
by ArndW
arsh14, welcome to DSXchange. I am not familiar with the rolllike function and can guess at what you wish to do but cannot be certain. Perhaps you could explain the logic in text form and you will probably get more accurate and quicker responses to your question.
Posted: Mon Jan 07, 2008 12:03 pm
by ray.wurlod
This will almost certainly need to done using stage variables in a Transformer stage that has its input sorted (and partitioned) by the key columns. A Sort stage can generate a key change column, which may prove useful.
Posted: Mon Jan 07, 2008 12:06 pm
by arsh14
[quote="ArndW"]arsh14, welcome to DSXchange. I am not familiar with the rolllike function and can guess at what you wish to do but cannot be certain. Perhaps you could explain the logic in text form and you will probably get more accurate and quicker responses to your question.[/quote]
What we are trying to do is to reduce the number of output rows by combining adjacent dates into a single row. So if we have D1 as 2006-01-01 and D2 as 2006-12-31 for Row1 and D1 as 2007-01-01 and D2 as 2007-12-31 for Row 2 (The diff between the D2 of Row1 and D1 of Row2 is just a day) and , the output should be a single row with D1=2006-01-01 and D2 as 2007-12-31 for a particular key combination.
Posted: Mon Jan 07, 2008 12:51 pm
by ray.wurlod
arsh14, by private email wrote:I am not able to see your reply since it is part of the premium content. Is there a way that you could send your reply to <<private email address given>>?
Yes, but I won't.
Premium content is one of the ways that the hosting and bandwidth costs of DSXchange are met. I have no intention of prejudicing that.
Premium membership is not expensive - less than 30c (Rs12) per day. It's definitely worth it. There is a link to information about corporate discounts from the DSXchange home page.