topic Re: Date differences within a column in SAS Enterprise Guide

Date differences within a column

CesarOmarHR — Wed, 04 Sep 2013 22:28:06 GMT

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

Re: Date differences within a column

Fugue — Wed, 04 Sep 2013 22:36:03 GMT

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

Re: Date differences within a column

Haikuo — Wed, 04 Sep 2013 22:36:36 GMT

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

Re: Date differences within a column

CesarOmarHR — Wed, 04 Sep 2013 22:38:54 GMT

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

Thanks

Re: Date differences within a column

CesarOmarHR — Wed, 04 Sep 2013 22:40:41 GMT

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

Re: Date differences within a column

art297 — Wed, 04 Sep 2013 22:45:53 GMT

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.

Re: Date differences within a column

Fugue — Wed, 04 Sep 2013 22:49:40 GMT

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

Re: Date differences within a column

Reeza — Wed, 04 Sep 2013 23:48:21 GMT

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

Re: Date differences within a column

TomKari — Thu, 05 Sep 2013 03:27:53 GMT

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

Re: Date differences within a column

fbatch — Thu, 05 Sep 2013 08:57:38 GMT

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.

Re: Date differences within a column

Fugue — Thu, 05 Sep 2013 20:54:10 GMT

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;

Re: Date differences within a column

Reeza — Thu, 05 Sep 2013 21:04:33 GMT

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

Re: Date differences within a column

CesarOmarHR — Thu, 05 Sep 2013 21:17:43 GMT

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

Re: Date differences within a column

Fugue — Thu, 05 Sep 2013 21:23:02 GMT

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