BookmarkSubscribeRSS Feed
anirudhs
Obsidian | Level 7

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.

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

anirudhs
Obsidian | Level 7
How can we do that ..can you explain with the code please
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

anirudhs
Obsidian | Level 7

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.

Shmuel
Garnet | Level 18

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 ?

Shmuel
Garnet | Level 18
I have merged your two posts into one. What format is your input - text like LOG file or a SAS dataset ?
anirudhs
Obsidian | Level 7

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.

 

anirudhs
Obsidian | Level 7
actually above two solutions are been accepted as to fetch the specific record from the dataset.
Shmuel
Garnet | Level 18

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 ?

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!

How to Concatenate Values

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.

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
  • 10 replies
  • 1268 views
  • 0 likes
  • 4 in conversation