topic Re: need to extract the ticket number from the variable in SAS Programming

need to extract the ticket number from the variable

radha009 — Mon, 14 May 2018 16:11:17 GMT

Can anyome help me with the code to extract a ticket number from variable name ' Request item' , data is as below.

number Request_item

1 This request is part of UAT Ticket# RITM0157177

2 Commit result for UAT RITM0180513

Expected results

number request_item ticket

1 This request is part of UAT Ticket# RITM0157177 RITM0157177

2 Commit result for UAT RITM0180513 RITM0180513

Thank you.

Re: need to extract the ticket number from the variable

Cynthia_sas — Mon, 14 May 2018 16:44:24 GMT

Hi:
Is the ticket number ALWAYS in request_item? Is it ALWAYS the last piece of the string? Could there be text AFTER the ticket number? Does the ticket number ALWAYS start with RIT or RITM?

Cynthia

Re: need to extract the ticket number from the variable

shahparth260 — Mon, 14 May 2018 16:52:55 GMT

  data have;
  	
  x="This request is part of UAT Ticket# RITM0157177";
  y="Commit result for UAT RITM0180513";

  run;


data want;
	 set have;

	 upx=strip(upcase(x));
	 upy=strip(upcase(y));

	 indx1=index(upx,"RITM");
	 indy1=index(upy,"RITM");

	 lenx1=length(substr(upx,x1));
	 leny1=length(substr(upy,y1));
	
	 subx=substr(upx,indx1,lenx1);
	 suby=substr(upy,indy1,leny1);

	 keep subx suby;
 run;

I can make it little shorter but I want to give idea for this that's why it's little long code

I hope it helps

Thanks

Re: need to extract the ticket number from the variable

shahparth260 — Mon, 14 May 2018 16:54:50 GMT

Though Cynthia is absolutely right with her questions for you but I give you just an idea

Re: need to extract the ticket number from the variable

nketata — Mon, 14 May 2018 16:59:21 GMT

You have to extract the 11 last characters from Request_item column

DATA want; set have;

ticket = substr(Request_item, length(Request_item)-11);run;

**I assume here the ticket number is always displayed at the end of Request_item***

Re: need to extract the ticket number from the variable

radha009 — Mon, 14 May 2018 17:39:01 GMT

No it will not be always end. it will any where

Re: need to extract the ticket number from the variable

radha009 — Mon, 14 May 2018 19:40:33 GMT

The ticket number is always in the request item. The text will be there before or after the ticket number. It always starts with RITM and 11 length.

Re: need to extract the ticket number from the variable

ballardw — Mon, 14 May 2018 22:13:22 GMT

Try

  ticket = substr(x,FIND(x,'RITM'),11);

where X is the name of YOUR variable.

WARNING: This really only works reasonably well because RITM is an infrequent letter combination in English and the code is supposedly always in upper case. If the code letters at the start were more common such as AT even the upper case might be insufficient to find the ticket if billed to or for AT&T or any other organization with AT in their name or a person that uses those initials frequently. I am sure there are other cases to consider as well.