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
César
I'm not sure what you mean. Can you provide an example of how you want the output data to look?
I would like it to look like the "DaysDifference" column
Thanks
I see -- I thought that was your input data already looked like.
data have;
input Client PurchaseDate :date9.;
format purchasedate date9.;
cards;
1 01jan2013
1 05jan2013
1 10jan2013
1 12jan2013
2 01jan2013
2 31jan2013
2 05feb2013
2 15feb2013
;
data want;
set have;
by client;
daysdifference=ifn(first.client,.,dif(purchasedate));
run;
Haikuo
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
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.
Tom
This can be done in PROC SQL although I agree it is not as intuitive or elegant as the data step method:
proc sql;
create table want as
select t1.*
, q1.purchasedate as next_dt
, q1.purchasedate - t1.purchasedate as datedif
from test t1
left join
( 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
;
quit;
Is there a way to do subqueries like that in EG Query Builder or do you have to go into code anyways?
@Reeza
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.
Hi,
with your data, i suggested you to try this ...
data nouv (drop=lstdat);
retain lstdat;
set have;
by client;
if first.client then lstdat=.;
daysdifference=intck('days',lstdat,purchasedate);
lstdat=purchasedate;
run;
FB.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.