Hi, need a code where the data is to be extracted with following conditions , where assigned to is getting changed, with status= assigned and assigned dtm may change or remain same.
following is the data.
TicketId,AssignedTo,AssignedDTM,Status,Resolution,UpdatedBy,UpdatedDTM,ResolutionDate
63538,824,30MAR2017:13:48:00.000,Assigned,824 03APR2017:09:37:32.347
63538,824,30MAR2017:13:48:00.000,Assigned,790 30MAR2017:13:48:48.080
63538,824,30MAR2017:13:48:00.000,Assigned,790 30MAR2017:13:48:48.080
63538,848,03APR2017:09:38:00.000,Assigned,824 03APR2017:09:38:50.517
63538,866,03APR2017:17:06:00.000,Assigned,848 03APR2017:17:06:33.553
63538,824,03APR2017:18:19:00.000,Assigned,866 03APR2017:18:19:59.447
63538,824,03APR2017:18:19:00.000,Assigned,773 05APR2017:11:10:42.557
63538,824,03APR2017:18:19:00.000,Assigned,773 12APR2017:20:45:24.727
63538,820,10MAY2017:19:48:00.000,Assigned,773 11MAY2017:00:20:27.800
63538,820,10MAY2017:19:48:00.000,Assigned,820 11MAY2017:15:12:08.537
63538,820,10MAY2017:19:48:00.000,Assigned,820 10MAY2017:19:48:08.777
63538,820,10MAY2017:19:48:00.000,Assigned,820 10MAY2017:20:00:17.180
63538,784,11MAY2017:15:12:00.000,Assigned,820 11MAY2017:15:12:33.460
63538,784,11MAY2017:15:12:00.000,Assigned,790 15MAY2017:09:58:00.933
63538,824,22JUN2017:19:18:00.000,Assigned,784 22JUN2017:19:18:33.717 16MAY2017:11:18:09.013
63538,824,22JUN2017:19:18:00.000,Assigned,790 22JUN2017:19:25:11.840 16MAY2017:11:18:09.013
63538,849,30JUN2017:19:17:00.000,Assigned,790 30JUN2017:19:17:54.043 16MAY2017:11:18:09.013
63538,824,03JUL2017:19:24:00.000,Assigned,849 03JUL2017:19:24:10.420 16MAY2017:11:18:09.013
63538,824,03JUL2017:19:24:00.000,Assigned,824 11JUL2017:23:22:41.703 16MAY2017:11:18:09.013
63538,784,11JUL2017:23:23:00.000,Assigned,824 11JUL2017:23:23:43.790 16MAY2017:11:18:09.013
63538,784,11JUL2017:23:23:00.000,Assigned,784 17JUL2017:12:07:30.920 16MAY2017:11:18:09.013
63538,784,11JUL2017:23:23:00.000,Assigned,773 13JUL2017:09:15:41.757 16MAY2017:11:18:09.013
only the record highlighted in red should be taken. where assigned to is been changed and the assigned date and updated dtm.
Not really following. Post test data in the form of a datastep as text in a code window ({i} above post). It sounds to me like you can just compare current values with lag() variables and then output if different?
I am not typing that data in to write code for you. Documentation on how to use the lag() function is in the manual:
https://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#n0l66p5oqe...
Here are some examples:
http://support.sas.com/resources/papers/proceedings09/055-2009.pdf
This is an expansion of the solution I already gave you in https://communities.sas.com/t5/Base-SAS-Programming/How-to-read-next-record-for-consecutive-rows-of-...
Please re-use the code and expand it; if the result does not match your expectations, post your dataset in a data step (conversion macro found in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...), and the code you tried.
following is the data ,
sorting on the historydtm and select only TAT dates changed on latest history dtm.
TicketId AssignedTo TAT HistoryDTM
91604 790 29JUN2017:00:00:00.000
91604 0 29JUN2017:00:00:00.000 28JUN2017:18:45:23.473
91604 0 29JUN2017:00:00:00.000 28JUN2017:18:45:51.090
91604 780 29JUN2017:00:00:00.000 28JUN2017:19:00:01.703
91604 780 29JUN2017:00:00:00.000 28JUN2017:19:12:30.097
91604 790 29JUN2017:00:00:00.000 29JUN2017:11:05:46.253
91604 780 30JUN2017:00:00:00.000 29JUN2017:14:15:25.720
91604 790 30JUN2017:00:00:00.000 29JUN2017:15:37:43.173
91604 790 30JUN2017:00:00:00.000 30JUN2017:12:30:52.953
91604 790 29JUN2017:00:00:00.000 30JUN2017:12:31:09.893
data set should contain the above highlighted data only where assigned to is also been changing.
You have highlighted two lines but it is not clear what are the rules to subset those lines.
Do you mean to extract lines
where HistoryDTM is between '29jun2017:11:00:00'dt and '29jun2017:15:00:00'dt ?
The AssignedTo is changing from 790 => 0 => 780 => 790 so why only those two lines should be extracted and not other changes ?
Hi @Shmuel its a sas data set which is been fetch from sql db as it maintains the history of the changes saved on ticket generation.
we have to only take those data rows whose TAT is been changed from previous to new date&time.
You want to " take those data rows whose TAT is been changed from previous to new date&time."
Please define what are the ID variables of those rows ?
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.