topic Re: character string data manipulation help? in SAS Programming

character string data manipulation help?

MarkWik — Mon, 19 Jan 2015 10:54:06 GMT

Hi,

Requesting character string data manipulation help? I have a character variable with values shown below:/*the dataset has 1 million rows*/

Variable_A

AkBGL.0130.AE0010201*AED*AC.1.TR.AED.10011............AE0010201*AED1001199990201*DEBIT

AkBGL.0130.AE0010201*AED*AC.1.TR.AED.10011............AE0010201*ZZY /*this kind ending with ZZY should be deleted*/

AkBGL.0130.AE0010201*AED*TOTAL/*this kind of obs ending with TOTAL to be kept*/

AkBGL.0130.AE0010201*ZZY/*this kind ending with ZZY should be deleted*/

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.200004.1.17.2400.AE....AE0010201*LD1404900037*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.200004.1.17.2400.AE....AE0010201*LD1424400010*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.200004.1.17.2400.AE....AE0010201*ZZY/*this kind ending with ZZY should be deleted*/

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*LD1403400034*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*LD1409000161*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*LD1409900008*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*LD1412000039*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*ZZY/*this kind ending with ZZY should be deleted*/

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.2.2400.AE....AE0010201*LD1231800018*LIVEDB

Want:/*two new variables*/

new_variable1 should have values in green extracted from the character string.

new_variable2 should have values in blue extracted from the character string

Re: character string data manipulation help?

Ksharp — Mon, 19 Jan 2015 11:59:22 GMT

data have;

input Variable_A : $200.;

cards;

AkBGL.0130.AE0010201*AED*AC.1.TR.AED.10011............AE0010201*AED1001199990201*DEBIT

AkBGL.0130.AE0010201*AED*AC.1.TR.AED.10011............AE0010201*ZZY

AkBGL.0130.AE0010201*AED*TOTAL

AkBGL.0130.AE0010201*ZZY

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.200004.1.17.2400.AE....AE0010201*LD1404900037*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.200004.1.17.2400.AE....AE0010201*LD1424400010*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.200004.1.17.2400.AE....AE0010201*ZZY

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*LD1403400034*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*LD1409000161*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*LD1409900008*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*LD1412000039*LIVEDB

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.10.2400.AE....AE0010201*ZZY

AkBGL.0920.AE0010201*AED*LD.1.TR.AED.21069.1017.AE.5Y.400022.1.2.2400.AE....AE0010201*LD1231800018*LIVEDB

;

run;

data want;

set have;

length var1 var2 $ 100;

if scan(strip(Variable_A),-1,'*') = 'ZZY' then delete;

re=prxparse('/(?<=AED\.)(\d+).*\*(\w+)$/o');

if prxmatch(re,strip(Variable_A)) then do;

var1=prxposn(re,1,strip(Variable_A));

var2=prxposn(re,2,strip(Variable_A));

end;

drop re;

run;

Xia Keshan

Re: character string data manipulation help?

MarkWik — Tue, 20 Jan 2015 05:13:46 GMT

Xia Keshan, You are a genius and a SAS super star. Thank you so much. I need one more help in manipulation using the same input, I'll phrase that as a separate question and will look forward to your help if you can.

Many thanks from Down under,

Mark Wik

(from Australia)

Re: character string data manipulation help?

Ksharp — Tue, 20 Jan 2015 06:52:26 GMT

OK. If I could . Start a new session . Other SAS super stars would be able to offer you good advice too.

Xia Keshan

Re: character string data manipulation help?

Reeza — Tue, 20 Jan 2015 06:59:08 GMT

please mark question as answered as well

Re: character string data manipulation help?

MarkWik — Tue, 20 Jan 2015 08:21:50 GMT

Indeed I want to mark the question answered and embrace the great genius, but the green and orange pointers isn't appearing in my screen for some reason despite the fact i logged in again and again to see if i can spot that. I guess perhaps i made a mistake to have missed out mark as a question when i first wrote the request. Please accept my heartfelt apologies