Thanks @PaigeMiller.. This one worked as charm... Thanks a lot. You are a GEM !! ... View more

Hi @PaigeMiller,    Sorry for that... Basically My task is to clean the transaction lines. My ultimate goal is to keep only the merchant names in the transaction string and hence i want to remove all these unwanted phrases. So basically, which ever transaction line finds this entire phrase as defined in my remove list should be removed from the transaction variable in the HAVE dataset. I dont want to break them by each single word as couple of other phrases might have post code and in removing number separatelly there are chances that some other wanted number might have gone. So which I decided to create this removal parameter list which should be looked for as a whole phrase and if found then remove it.   VISA DEBIT PURCHASE CARD 8013 VISA DEBIT PURCHASE CARD 4059 VISA DEBIT PURCHASE CARD 7005 VISA DEBIT PURCHASE CARD 6008 VISA DEBIT PURCHASE CARD 4095 INTERNET BANKING FUNDS TFER EFTPOS COUNTRY SUBURB COUNTRY     And so is the below result after removing all the phrases from the HAVE dataset.   MERCHANT_A AREA1 MERCHANT_B MERCHANT_X AREA10 2271 MERCHANT_Y  MERCHANT_Y AREA100 57898 MERCHANT_X MERCHANT_Z AREA1 AREA1  MERCHANT_Z MERCHANT_Z TRANSFER REF:NNN FROM A/C:NNNNNN COSTCO WHOLESALE   Hope I was able to explain you in a much better way.....    Thanks mate..   Rgds, AK   ... View more

Hi @PaigeMiller, yes you are partially right on the bad example. Its not that i need to trim everything before word MERCHANT, but what ever i define in the remove list should be removed from the Have dataset. Few more example as below.   data have; input transaction & $100.; cards; INTERNET BANKING FUNDS TFER TRANSFER REF:NNN FROM A/C:NNNNNN EFTPOS COSTCO WHOLESALE COUNTRY SUBURB COUNTRY ; data remove_list; input rule_id & $100.; cards; INTERNET BANKING FUNDS TFER EFTPOS COUNTRY SUBURB COUNTRY ;   data want; input transaction & $100.; cards; TRANSFER REF:NNN FROM A/C:NNNNNN COSTCO WHOLESALE ;   ... View more

Wow... superb...  This worked exactly as i wanted.   Thanks a lot. ... View more

Thanks Reeza.. It worked.. I was doing proc transpose before but guess i was doing something wrong and was not getting the right output. But this time i got it..    Many thanks,   Rgds, Anil   ... View more

I did it using proc transpose as you suggested before and it worked for my requirement.   proc transpose data=sample out=work.trnstransposed let name=source label=label; by rule_seq_no; id lhs_term; var expression; run; quit;   ... View more

Hi Reeza,    I was trying by first.var and last.var but not in a good shape at all. Here's the sample dataset.    data input; infile datalines dsd truncover; input rule_set_nm:$54. rule_seq_no:BEST2. expression_type:$9. expression_order:BEST1. conditional_nm:$6. lhs_term:$26. expression:$158.; datalines4; PORT_1,1,CONDITION,1,if,ORIGIN, PORT_1,1,CONDITION,2,if,CURRENT,"IN (""G10"", ""G9"", ""H2"", ""H3"", ""H4"", ""H5"", ""H6"", ""H7"", ""H8"", ""I"", ""I1"")" PORT_1,1,ACTION,1,if,STAGE,"""Stage3""" PORT_1,2,CONDITION,1,elseif,ORIGIN,"IN(""A99"", ""A98"", ""A95"")" PORT_1,2,CONDITION,2,elseif,CURRENT,"IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"")" PORT_1,2,ACTION,1,elseif,STAGE,"""Stage1""" PORT_1,3,CONDITION,1,elseif,ORIGIN,"IN(""A92"", ""A90"", ""G10"", ""G11"", ""G9"", ""H8"", ""H7"", ""H6"", ""H5"", ""H4"", ""H3"", ""H2"", ""I1"", ""I"")" PORT_1,3,CONDITION,2,elseif,CURRENT,"IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"")" PORT_1,3,ACTION,1,elseif,STAGE,"""Stage1""" PORT_1,4,CONDITION,1,elseif,ORIGIN,"IN(""B87"", ""B82"", ""B78"", ""B77"")" PORT_1,4,CONDITION,2,elseif,CURRENT,"IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"")" PORT_1,4,ACTION,1,elseif,STAGE,"""Stage1""" PORT_1,5,CONDITION,1,elseif,ORIGIN,"IN(""C72"", ""C67"")" PORT_1,5,CONDITION,2,elseif,CURRENT,"IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"")" PORT_1,5,ACTION,1,elseif,STAGE,"""Stage1""" PORT_1,6,CONDITION,1,elseif,ORIGIN,"IN(""C62"")" PORT_1,6,CONDITION,2,elseif,CURRENT,"IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"", ""D49"")" PORT_1,6,ACTION,1,elseif,STAGE,"""Stage1""" PORT_1,7,CONDITION,1,elseif,ORIGIN,"IN(""C60"", ""D57"")" PORT_1,7,CONDITION,2,elseif,CURRENT,"IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"", ""D49"", ""D47"", ""D46"")" PORT_1,7,ACTION,1,elseif,STAGE,"""Stage1""" PORT_1,8,CONDITION,1,elseif,ORIGIN,"IN(""D52"", ""D49"", ""D47"")" PORT_1,8,CONDITION,2,elseif,CURRENT,"IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"", ""D49"", ""D47"", ""D46"", ""D45"", ""D44"", ""D43"")" PORT_1,8,ACTION,1,elseif,STAGE,"""Stage1""" PORT_1,9,CONDITION,1,elseif,ORIGIN,"IN (""D46"", ""D45"", ""D44"", ""D43"", ""D42"", ""E35"", ""F21"", """", ""N/A"")" PORT_1,9,CONDITION,2,elseif,CURRENT,"IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"", ""D49"", ""D47"", ""D46"", ""D45"", ""D44"", ""D43"", ""D42"")" PORT_1,9,ACTION,1,elseif,STAGE,"""Stage1""" PORT_1,10,ACTION,1,elseif,STAGE,"""Stage2""" ;;;; data output; length conditional_nm $ 6 origin $ 89 current $ 153 stage $ 8 ; format conditional_nm $char6. origin $char89. current $char153. stage $char8. ; informat conditional_nm $char6. origin $char89. current $char153. stage $char8. ; infile datalines4 dlm='7f'x missover dsd ; input conditional_nm : $char6. origin : $char89. current : $char153. stage : $char8. ; datalines4; if"IN (""G10"", ""G9"", ""H2"", ""H3"", ""H4"", ""H5"", ""H6"", ""H7"", ""H8"", ""I"", ""I1"")""""Stage3""" elseif"IN(""A99"", ""A98"", ""A95"")""IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"")""""Stage1""" elseif"IN(""A92"", ""A90"", ""G10"", ""G11"", ""G9"", ""H8"", ""H7"", ""H6"", ""H5"", ""H4"", ""H3"", ""H2"", ""I1"", ""I"")""IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"")""""Stage1""" elseif"IN(""B87"", ""B82"", ""B78"", ""B77"")""IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"")""""Stage1""" elseif"IN(""C72"", ""C67"")""IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"")""""Stage1""" elseif"IN(""C62"")""IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"", ""D49"")""""Stage1""" elseif"IN(""C60"", ""D57"")""IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"", ""D49"", ""D47"", ""D46"")""""Stage1""" elseif"IN(""D52"", ""D49"", ""D47"")""IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"", ""D49"", ""D47"", ""D46"", ""D45"", ""D44"", ""D43"")""""Stage1""" elseif"IN (""D46"", ""D45"", ""D44"", ""D43"", ""D42"", ""E35"", ""F21"", """", ""N/A"")""IN(""A99"", ""A98"", ""A95"", ""A92"", ""A90"",""B87"", ""B82"", ""B78"", ""B77"",""C72"", ""C67"",""C62"", ""C60"", ""D57"", ""D52"", ""D49"", ""D47"", ""D46"", ""D45"", ""D44"", ""D43"", ""D42"")""""Stage1""" elseif  """Stage2""" ;;;;   ... View more

Thanks Chris. This is what exactly i want, but now how do i format this, like if i have to pass on as below format sizekmgt10.2;   So if a number is 1599511627776 then the output should be  1.59 TB and  if i use format  sizekmgt10.4; then the output should be 1.5995 TB     ... View more