Divide text into columns

[laxmanpai]

Hi Team,

I have a Text below;

abcd15ppppp

bvcsf24dsfd

cadadada30fdsfdsf

The final output should look like below with 3 columns:

col1                col2                   col3

abcd               15                        ppppp

bvcsf                24                       dsfd

cadadada        30                      fdsfdsf

Could you please help me here

Regards

Laxman 

[PaigeMiller]

data want;
    set have;
    where1=anydigit(text);
    where2=anyalpha(text,where1);
    string1=substr(text,1,where1-1);
    string2=substr(text,where1,where2-where1);
    string3=substr(text,where2);
run;

[LinusH]

And of course thee are multiple ways of doing it in SAS:

data have;
	length text $20;
	input text;
	datalines;
abcd15ppppp
bvcsf24dsfd
cadadada30fdsfdsf
;
run;

data want;
	set have;
	col1 = scan(text,1,,'D');
	col2 = scan(text,1,,'AI');
	col3 = scan(text,2,,'D');
run;

[laxmanpai]

Hi ,

I did not get the code you have written. Could you please elaborate what these 3 statements do and how do they get split

data want;
	set have;
	col1 = scan(text,1,,'D');
	col2 = scan(text,1,,'AI');
	col3 = scan(text,2,,'D');
run;

[Tom]

---

@laxmanpai wrote:

Hi ,

I did not get the code you have written. Could you please elaborate what these 3 statements do and how do they get split

data want;
	set have;
	col1 = scan(text,1,,'D');
	col2 = scan(text,1,,'AI');
	col3 = scan(text,2,,'D');
run;

---

Read the SCAN() function documentation.  Make sure the check the meaning of the A,D and I modifiers.

[laxmanpai]

Hi,

The modifier 'A' - Adds alphabetic characters to the list of characters

                         'D' - Adds digits to the list of characters.

But

col1 = scan(text,1,,'D');

will output Peter but 'D' represents the Digits right ?

Could you please clarify

[laxmanpai]

Hi,

the first line in the input statement is as follows :

abcd15ppppp

col1= scan(text,1, , 'D') should result in 15 right?, but it would output abcd. 

Could you please clarify.

[PaigeMiller]

The result of this command should be abcd, not 15. The 'D' modifier indicates digits are the separator between "words", so the first word ends when the 15 appears, the first word is abcd.

[laxmanpai]

Thanks a lot for clarifying.

[Tom]

The D (and A) modifiers add characters to the list of delimiter characters.

D means Digits and A means Alphabet.

So the command 

col1= scan(text,1, , 'D');

is the same as the command

col1= scan(text,1,'0123456789')

Remember that when there are multiple adjacent delimiter it only causes one split in the list.

So

scan('abcd15xyz',1,'0123456789')

Will return abcd as that is the first "word" before the first delimiter.

Also when the string starts with the delimiters the first word is after the first block of delimiters.  

So 

scan('abcd15xyz',1,,'A')

will return '15' since the leading block of letters is skipped over and the trailing block of letters marks the end of the first word.

The way to remember this is that it is basically doing what you would do when reading a space delimited list of words.  For both of these strings:

ONE TWO THREE
      ONE       TWO     THREE

The first word is "ONE" and the second word is "TWO".