Desktop productivity for business analysts and programmers

Date differences within a column

Reply
Contributor
Posts: 36

Date differences within a column

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

Super Contributor
Posts: 307

Re: Date differences within a column

I'm not sure what you mean. Can you provide an example of how you want the output data to look?

Contributor
Posts: 36

Re: Date differences within a column

I would like it to look like the "DaysDifference" column

Thanks

Super Contributor
Posts: 307

Re: Date differences within a column

I see -- I thought that was your input data already looked like.

Respected Advisor
Posts: 3,156

Re: Date differences within a column

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

Contributor
Posts: 36

Re: Date differences within a column

Alright i'll try to fix it into the advanced querying section within the Enterprise Guide options.

PROC Star
Posts: 7,437

Re: Date differences within a column

I would just insert a code node with 's suggested code.  I think that the querying section uses SQL and, for what you want to do, SQL by definition doesn't use record order.

Contributor
Posts: 36

Re: Date differences within a column

Thank you all, i really appreciate it and agree with you , there are plenty of options to choose from.

i'm just working on replacing the 10 record data with my sas database.

Thanks a lot

Cesar

Super User
Posts: 19,167

Re: Date differences within a column

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

PROC Star
Posts: 1,146

Re: Date differences within a column

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

Super Contributor
Posts: 307

Re: Date differences within a column

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;

Super User
Posts: 19,167

Re: Date differences within a column

Is there a way to do subqueries like that in EG Query Builder or do you have to go into code anyways?

Super Contributor
Posts: 307

Re: Date differences within a column

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

Occasional Contributor
Posts: 7

Re: Date differences within a column

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.

Ask a Question
Discussion stats
  • 13 replies
  • 375 views
  • 1 like
  • 7 in conversation