topic Re: Divide text into columns in SAS Programming

Divide text into columns

laxmanpai — Fri, 27 Sep 2024 12:17:36 GMT

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

Re: Divide text into columns

PaigeMiller — Fri, 27 Sep 2024 12:26:28 GMT

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;

Re: Divide text into columns

LinusH — Fri, 27 Sep 2024 12:30:38 GMT

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;

Re: Divide text into columns

laxmanpai — Fri, 27 Sep 2024 12:43:39 GMT

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;

Re: Divide text into columns

Tom — Fri, 27 Sep 2024 13:01:22 GMT

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

Re: Divide text into columns

laxmanpai — Mon, 30 Sep 2024 10:17:06 GMT

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

Re: Divide text into columns

laxmanpai — Mon, 30 Sep 2024 10:25:15 GMT

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.

Re: Divide text into columns

PaigeMiller — Mon, 30 Sep 2024 10:40:50 GMT

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.

Re: Divide text into columns

laxmanpai — Mon, 30 Sep 2024 10:50:59 GMT

Thanks a lot for clarifying.

Re: Divide text into columns

Tom — Mon, 30 Sep 2024 13:56:18 GMT

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.

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.

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