I have the following data set:
Row - Customer - Renew Date - Type of Renewal - Days
1 - A - June 10, 2010 - X
2 - A - May 01, 2011 - Y
3 - B - Jan 05, 2010 - Y
4 - B - Dec 10, 2010 - Z
5 - B - Dec 10, 2011 - X
Is there a way I can put a condition in query builder where it subtracts row 1 from row 2 for each customer, so that I can have the # of "Days" after which a customer renew's their membership ?
Basically, I need help in subtracting rows in query builder.
Please adivse.
This isn't too hard if you write a datastep. I don't know that it's easily done in Query Builder.
data have;
informat renew_date ANYDTDTE.;
format renew_date DATE9.;
infile datalines dlm='-';
input Row Customer $ Renew_Date Renewal_Type $;
datalines;
1 - A - June 10, 2010 - X
2 - A - May 01, 2011 - Y
3 - B - Jan 05, 2010 - Y
4 - B - Dec 10, 2010 - Z
5 - B - Dec 10, 2011 - X
;;;;
run;
data want;
set have;
by customer;
retain prev_days; *retain the value of prev_days from one row to the next;
if first.customer then days_since=0; *initialize days_since to zero for each customer's first record;
else days_since=renew_date-prev_days; *otherwise set it to the difference;
output; *output the current record;
prev_days=renew_date; *now change prev_days to the renewal date so the next record has it;
run;