Desktop productivity for business analysts and programmers

Need to get a recursive character (C#) - C#123456

Reply
Occasional Contributor
Posts: 13

Need to get a recursive character (C#) - C#123456

Hi,

 

I need to get a recursive character contains (C#) followed by 6 digit integer in the field “Description”, I have used a below code in proc sql, i will be getting the first 8 character with the below code, how can i get the "n"th character which contains "C#" followed by interger. pls refer the example below,

 

proc sql;

select

number, 

substr(desc,index(desc,"C#"),8)

from mast

where

substr(desc,index(desc,"C#")+2,1) in ('1','2','3','4','5','6','7','8','9','0');  //to get C# followed by interger only.

run;

 

desc - column_name

 

Eg : 04-06-2015 20:20:39 - dummy (notes) dummy team._   Please reun the process dummy  04-07-2015 21:15:32 -._  Please reun the process (notes) ABCD are C#123456 and C#654321 

 

Pls help me on this, thanks in advance.

Grand Advisor
Posts: 17,342

Re: Need to get a recursive character (C#) - C#123456

That is really hard to follow. Can you please show us what you have and what you need. Thanks for including the SQL that you have already. 

Occasional Contributor
Posts: 13

Re: Need to get a recursive character (C#) - C#123456

Thanks for your reply, i will be getting an output as

 

I have the below,

 

NumberDesc
INC142584 C#123456

 

I need to get,

 

NumberDesc
INC142584 C#123456 C#654321

 

 

I need to get the second (C#654321) one as well, there may be multiple C# number in the description, I need to get all the C# number in the field description. Pls assist me, thanks.

Grand Advisor
Posts: 17,342

Re: Need to get a recursive character (C#) - C#123456

What do you start with?

Is it:

 


vivekarun12 wrote:

Thanks for your reply, i will be getting an output as

 

I have the below,

 

Number Desc
INC142584  C#123456

 

 


 

Respected Advisor
Posts: 3,834

Re: Need to get a recursive character (C#) - C#123456

[ Edited ]

@vivekarun12

Here a RegEx which should return what you're after for up to 6 digits after a C#

data have;
infile datalines dsd truncover;
input Number:$20.	Desc:$100.;
datalines;
INC142584,C#123456
INC142584,C#123456 xxx C#789 yyy
;
run;

data want;
  set have;
  length desc2 $200;
  desc2=prxchange('s/(c#)(\d)(\d)(\d?)(\d?)(\d?)(\d?)(\d?)/\1\2\3\4\5\6\7 \1\7\6\5\4\3\2/oi',-1,desc);
run;
Occasional Contributor
Posts: 13

Re: Need to get a recursive character (C#) - C#123456

hi,

 

Thanks for the post, for these datalines “INC142584,C#123456 xxx C#789 yyy” I need only “C#123456 C#789”, I have to ignore all the characters (xxx yyy, etc.,) except C# followed by numbers. I don’t need a reverse string.

 

Thanks in advance.

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: Need to get a recursive character (C#) - C#123456

See this is good example of not clearly stating your question.  Posting test data in the form of a datastep - which clearly shows your problem, and what you want at the end is essential to us who cannot guess what you are doing.  Now if your problem is a you now describe then a simple datastep:

data want;
  set have;
  length result $2000;
  do i=1 to countw(var1," ");
    if index(scan(var1,i," "),"C#") > 0 then result=catx(' ',want,scan(var1,i," "));
  end;
run;  

This will create a variable result which takes only words from var1 where they contain C#.

Grand Advisor
Posts: 17,342

Re: Need to get a recursive character (C#) - C#123456


vivekarun12 wrote:

 

Eg : 04-06-2015 20:20:39 - dummy (notes) dummy team._   Please reun the process dummy  04-07-2015 21:15:32 -._  Please reun the process (notes) ABCD are C#123456 and C#654321 

 


I think this may be what you're starting with? If so, this is much better done in a data step since you can loop the logic. 

 

I would recommend regular expressions, which I suck at, so instead I'll give you a reference to a good paper:

http://www2.sas.com/proceedings/forum2007/223-2007.pdf

 

You could probably use COUNTW to see how many # are in the text, and then loop that many times and check for the string. But to ensure it meets the pattern is more difficult without using regular expressions.

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: Need to get a recursive character (C#) - C#123456

Your questions will be answered far more acurate if you follow the guidance found by the Post button on new posts:

Post test data in the form of a datastep

Post example output required

Explain any logic between

Post code if you have it

 

At a guess:

data have;
  desc="C#123456 C#789564 C#122256";
run;

data want (drop=i word revword);
  set have;
  length final word revword $2000;
  final="";
  do i=1 to countw(desc," ");
    word=scan(desc,i," ");
    revword=cats("C#",reverse(substr(word,index(word,"#")+1)));
    final=catx(" ",final,word,revword);
  end;
run;

You could of course move the word and revword directly into the final= part, I spit it up to show the process.

Ask a Question
Discussion stats
  • 8 replies
  • 200 views
  • 1 like
  • 4 in conversation