topic Re: How to extract ticket number after # an only 8numbers ? in SAS Procedures

How to extract ticket number after # an only 8numbers ?

BETO — Wed, 18 Nov 2015 19:01:25 GMT

Hi I have a comment box that looks like this

Arrive 11/16/15 ticket #012345678 prod Id 45678

I need to just get the ticket# which is the 8 digits ...I need output to be 012345678.. I use compress an it combine it together. .. I read up on prxchange to extract but it's not working thanks for your assistance

Data table1;

Set table1;

Ticket =prxchange ("S/[^0-9]//",-1,text);

Re: How to extract ticket number after # an only 8numbers ?

Astounding — Wed, 18 Nov 2015 19:05:17 GMT

If you can rely on the first # being before the ticket number, it's easy:

data want;

set have;

length ticket $ 8;

ticket = scan(text, 2, '#');

run;

If you actually need 9 digits instead of 8 (you have 9 in your example), just change the length to $ 9.

Good luck.

Re: How to extract ticket number after # an only 8numbers ?

Steelers_In_DC — Wed, 18 Nov 2015 19:55:57 GMT

Similar solution but if I understand you correct you want another scan() in there:

data have;
infile cards dsd dlm='*****';
informat field $100.;
format field $100.;
input field$;
cards;
Arrive 11/16/15 ticket #012345678 prod Id 45678
;
run;

data want;
set have;
want = scan(scan(field,2,'#'),1);
run;

Re: How to extract ticket number after # an only 8numbers ?

Astounding — Wed, 18 Nov 2015 23:45:27 GMT

Steelers,

That's the purpose of having the LENGTH statement. There's only room to hold 8 characters in the new variable, so you don't have to worry about getting rid of extra characters with an additional SCAN function.

It does require that the ticket numbers are 8 characters long, however. (7 would be OK too, if the 8th character is a blank.)

Re: How to extract ticket number after # an only 8numbers ?

BETO — Wed, 18 Nov 2015 23:51:51 GMT

Hi Steelers it worked great but I do have a question the output has a semi colon :it's looks like this :012345678 I need the out put to be 012345678 ... thanks

Re: How to extract ticket number after # an only 8numbers ?

slchen — Thu, 19 Nov 2015 00:06:04 GMT

It is easy to get number with scan here, if you want to use regular expression, try this.

data _null_;
string="Arrive 11/16/15   ticket #012345678 prod Id 45678";
number=prxchange('s/.*(?<=#)(\d+).*/$1/',-1,string);
put number;
run;

Re: How to extract ticket number after # an only 8numbers ?

Astounding — Thu, 19 Nov 2015 00:06:08 GMT

Is there a colon in the incoming text, after the # ???