DATA Step, Macro, Functions and more

Extracting the data from log data.

Reply
Occasional Contributor
Posts: 13

Extracting the data from log data.

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.

Super User
Super User
Posts: 7,401

Re: Extracting the data from log data.

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?

Occasional Contributor
Posts: 13

Re: Extracting the data from log data.

How can we do that ..can you explain with the code please
Super User
Super User
Posts: 7,401

Re: Extracting the data from log data.

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

Super User
Posts: 6,936

Re: Extracting the data from log data.

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 13

extraction of records

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.

Trusted Advisor
Posts: 1,374

Re: extraction of records

[ Edited ]

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 ?

Trusted Advisor
Posts: 1,374

Re: extraction of records

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

Re: extraction of records

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.

 

Occasional Contributor
Posts: 13

Re: extraction of records

actually above two solutions are been accepted as to fetch the specific record from the dataset.
Trusted Advisor
Posts: 1,374

Re: extraction of records

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 ?

Ask a Question
Discussion stats
  • 10 replies
  • 167 views
  • 0 likes
  • 4 in conversation