rolllike function

arsh14 · Post by **arsh14** » Mon Jan 07, 2008 11:50 am

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.

ArndW · Post by **ArndW** » Mon Jan 07, 2008 11:54 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 07, 2008 12:03 pm

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.

arsh14 · Post by **arsh14** » Mon Jan 07, 2008 12:06 pm

[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.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 07, 2008 12:51 pm

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.