DATA Step, Macro, Functions and more

need to extract the ticket number from the variable

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

need to extract the ticket number from the variable

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.

 

 


Accepted Solutions
Solution
‎05-14-2018 05:55 PM
Super User
Posts: 13,498

Re: need to extract the ticket number from the variable

[ Edited ]

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.

View solution in original post


All Replies
SAS Super FREQ
Posts: 9,365

Re: need to extract the ticket number from the variable

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
Contributor
Posts: 70

Re: need to extract the ticket number from the variable

Posted in reply to Cynthia_sas
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.
Occasional Contributor
Posts: 15

Re: need to extract the ticket number from the variable

  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 

 

PS
Occasional Contributor
Posts: 15

Re: need to extract the ticket number from the variable

Posted in reply to shahparth260

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

 

PS
Contributor
Posts: 38

Re: need to extract the ticket number from the variable

[ Edited ]

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***

Contributor
Posts: 70

Re: need to extract the ticket number from the variable

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

Solution
‎05-14-2018 05:55 PM
Super User
Posts: 13,498

Re: need to extract the ticket number from the variable

[ Edited ]

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 152 views
  • 0 likes
  • 5 in conversation