BookmarkSubscribeRSS Feed
CesarOmarHR
Calcite | Level 5

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

13 REPLIES 13
Fugue
Quartz | Level 8

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

CesarOmarHR
Calcite | Level 5

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

Thanks

Fugue
Quartz | Level 8

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

Haikuo
Onyx | Level 15

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

CesarOmarHR
Calcite | Level 5

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

art297
Opal | Level 21

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.

CesarOmarHR
Calcite | Level 5

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

Reeza
Super User

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

TomKari
Onyx | Level 15

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

Fugue
Quartz | Level 8

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;

Reeza
Super User

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

Fugue
Quartz | Level 8

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

fbatch
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1448 views
  • 1 like
  • 7 in conversation