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.
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.
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.
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
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;
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.
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#.
@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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.