topic Re: Extracting the data from log data. in SAS Programming

Extracting the data from log data.

anirudhs — Mon, 31 Jul 2017 10:23:36 GMT

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.

Re: Extracting the data from log data.

RW9 — Mon, 31 Jul 2017 10:32:36 GMT

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?

Re: Extracting the data from log data.

anirudhs — Mon, 31 Jul 2017 10:45:33 GMT

How can we do that ..can you explain with the code please

Re: Extracting the data from log data.

RW9 — Mon, 31 Jul 2017 11:04:13 GMT

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#n0l66p5oqex1f2n1quuopdvtcjqb.htm

Here are some examples:

http://support.sas.com/resources/papers/proceedings09/055-2009.pdf

Re: Extracting the data from log data.

Kurt_Bremser — Mon, 31 Jul 2017 12:37:27 GMT

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-data/m-p/383574.

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-data-AKA-generate/ta-p/258712), and the code you tried.

extraction of records

anirudhs — Mon, 31 Jul 2017 12:46:21 GMT

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.

Re: extraction of records

Shmuel — Mon, 31 Jul 2017 13:13:55 GMT

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 ?

Re: extraction of records

Shmuel — Mon, 31 Jul 2017 13:37:00 GMT

I have merged your two posts into one. What format is your input - text like LOG file or a SAS dataset ?

Re: extraction of records

anirudhs — Tue, 01 Aug 2017 04:59:11 GMT

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.

Re: extraction of records

anirudhs — Tue, 01 Aug 2017 05:00:41 GMT

actually above two solutions are been accepted as to fetch the specific record from the dataset.

Re: extraction of records

Shmuel — Tue, 01 Aug 2017 05:08:39 GMT

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 ?