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.
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.
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
Though Cynthia is absolutely right with her questions for you but I give you just an idea
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***
No it will not be always end. it will any where
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.