How to chop a long Excel cell string

[Mirisage]

Hi Colleagues,

I was given the names of over 200 variables just as a single Excel string (attached).

I want to separate individual variable names as:

AA_VAR1

AA_VAR2

AA_VAR3

AA_VAR4

I separated them using text function of Excel like below but it took me the time almost same as doing manually.

=LEFT(B2,7)

=MID(B2,9,7)

=MID(B2,17,7)

I wonder if there is an automated way of doing using SAS?

Any help is appreciated.

Mirisage

[PGStats]

If you ultimately want to bring these names back in Excel, the simplest thing to do is to use the Data-Conversion operation with spaces as delimiters directly in Excel. You'll get your 241 variable names in the first row which you may then paste-transpose to get a single column.

If you insist on doing this operation in SAS, then look at the SCAN function. As in :

data want;

set have;

do i=1 by 1 until(missing(name));

     name = scan(names, i);

     if not missing(name) then output;

     end;

drop names;

run;

PG

[Mirisage]

Hi PG,

Many thanks.

I have just taken a piece of that string to try your SAS code. I cannot figure out why it doesn't work. Time permitting, I wonder if you could help again.

data have;

input names $250.;

cards;

AA_VAR1 AA_VAR2 AA_VAR3 AA_VAR4 AA_VAR5 AA_VAR6 AA_VAR7 AA_VAR8 AA_VAR11 AA_VAR12;

run;

data want;

set have;

do i=1 by 1 until(missing(name));

     name = scan(names, i);

     if not missing(name) then output;

     end;

drop names;

run;

Mirisage

[PGStats]

There were minor problems, corrected now:

data have;

length names $250;

input names & ;

cards;

AA_VAR1 AA_VAR2 AA_VAR3 AA_VAR4 AA_VAR5 AA_VAR6 AA_VAR7 AA_VAR8 AA_VAR11 AA_VAR12

;

data want;

length name $8;

set have;

do i=1 by 1 until(missing(name));

     name = scan(names, i);

     if not missing(name) then output;

     end;

drop names;

run;

PG

[Mirisage]

Hi PG,

A big thank for you. This is great! Worked well! Now I came to know about scan function too.

Mirisage