BookmarkSubscribeRSS Feed
vivekarun12
Calcite | Level 5

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.

8 REPLIES 8
Reeza
Super User

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. 

vivekarun12
Calcite | Level 5

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.

Reeza
Super User

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

 

 


 

Patrick
Opal | Level 21

@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;
vivekarun12
Calcite | Level 5

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.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1068 views
  • 1 like
  • 4 in conversation