09-04-2013 06:28 PM
Hi there, i'm trying to find, within the frame of the advanced expression of the "New Column" section, a way to generate a column that shows the intervals of lack of usage per client. How could i do it?
I appreciate the help.
Client PurchaseDate DaysDifference
1 01jan2013 .
1 05jan2013 4
1 10jan2013 5
1 12jan2013 2
2 01jan2013 .
2 31jan2013 30
2 05feb2013 5
2 15feb2013 10
09-04-2013 06:36 PM
input Client PurchaseDate :date9.;
format purchasedate date9.;
09-04-2013 06:45 PM
09-05-2013 05:17 PM
09-04-2013 07:48 PM
I'd insert a code node and do it that way. It took me at least 3 steps with EG with your data.
1. Run a rank procedure on the dataset to get a row number by id (enumerate by ID).
2. In Query Builder Create a new computed column that is rank - 1
3. In Query Builder Join table from step 2 to itself with rank in one column equal to rank-1 in other table, ensuring a left join
4. Add in a computed column for the lag calculation
09-04-2013 11:27 PM
I'm with everyone else. 1. Query builder only uses SQL. 2. While it is possible, but very ugly, to do record sequence operations in SQL, it's easy to the point of trivial to do it with a data step. 3. Reeza's approach is valid, and very clever, but is it really worth it?
I feel that this is one of the cases where using code outbalances the yuckyness of a pure EG task approach. Us SAS users are lucky, we have a variety of very powerful tools. I don't think we should tie one hand behind our backs for the sake of purity.
09-05-2013 04:54 PM
This can be done in PROC SQL although I agree it is not as intuitive or elegant as the data step method:
create table want as
, q1.purchasedate as next_dt
, q1.purchasedate - t1.purchasedate as datedif
from test t1
( select t2.*
from test t2 ) q1
on q1.client = t1.client
and q1.purchasedate =
( select min ( t3.purchasedate )
from test t3
where t3.client = q1.client
and t3.purchasedate > t1.purchasedate)
order by client, purchasedate
09-05-2013 05:23 PM
I've never seriously tried this type of approach in EG Query Builder, although I suspect it can be done. For more complex joins, I find that I can hand-write it quicker in a code object.
09-05-2013 04:57 AM
with your data, i suggested you to try this ...
data nouv (drop=lstdat);
if first.client then lstdat=.;