topic Re: Need to get a recursive character (C#) - C#123456 in SAS Enterprise Guide

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

vivekarun12 — Tue, 04 Jul 2017 15:52:51 GMT

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.

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

Reeza — Tue, 04 Jul 2017 16:08:37 GMT

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.

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

vivekarun12 — Tue, 04 Jul 2017 16:18:56 GMT

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

I have the below,

Number	Desc
INC142584	C#123456

I need to get,

Number	Desc
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.

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

Reeza — Tue, 04 Jul 2017 17:06:22 GMT

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

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

Reeza — Tue, 04 Jul 2017 17:15:18 GMT

@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.

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

RW9 — Tue, 04 Jul 2017 18:05:57 GMT

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.

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

Patrick — Thu, 06 Jul 2017 10:44:41 GMT

@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;

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

vivekarun12 — Fri, 07 Jul 2017 09:29:15 GMT

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.

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

RW9 — Fri, 07 Jul 2017 10:26:39 GMT

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#.