Solved
Contributor
Posts: 74

# Splitting Text into different variables/columns

Hi,

I have a case where a variable in my file looks like below:

VAR

--------

1234|2345|2222

1233|1111

11111|5555555|4444|6666|

I need to make this values look like:

var1           var2          Var3       Var4

-----           -------         --------      --------

1234       2345          2222

1233       1111

11111      5555555    4444        6666

Accepted Solutions
Solution
‎09-01-2011 02:58 PM
PROC Star
Posts: 8,167

## Splitting Text into different variables/columns

Numerous ways to do what you want.  e.g.:

data want (drop=_: i);

set have (rename=(var=_var));

array var(4);

i=1;

do until (scan(_var,i,"|") eq "");

var(i)=scan(_var,i,"|");

i+1;

end;

run;

All Replies
Solution
‎09-01-2011 02:58 PM
PROC Star
Posts: 8,167

## Splitting Text into different variables/columns

Numerous ways to do what you want.  e.g.:

data want (drop=_: i);

set have (rename=(var=_var));

array var(4);

i=1;

do until (scan(_var,i,"|") eq "");

var(i)=scan(_var,i,"|");

i+1;

end;

run;

Contributor
Posts: 74

Thank You!

Super User
Posts: 10,787

## Splitting Text into different variables/columns

Art's code need to pre-define the number of variables, But if you do not know what the number of variables should be ?

```data temp;
input var : \$2000.;
cards;
1234|2345|2222
1233|1111
11111|5555555|4444|6666|
;
run;
data _null_;
set temp end=last;
if _n_ eq 1 then call execute('data want;');
i=1;  _var=scan(var,i,'|');
do while(not missing(_var));
call execute(cats('var',i,'="',_var,'";') );
i+1; _var=scan(var,i,'|');
end;
call execute('output;call missing(of _all_); ');
if last then call execute('run;');
run;

```

Ksharp

Frequent Contributor
Posts: 93

## Re: Splitting Text into different variables/columns

[ Edited ]

ksharp, What if the dataset has a primary key?

PK,VAR

1,1234|2345|2222

2,1233|1111

3,11111|5555555|4444|6666|

I need to make this values look like:

pk              var1           var2          Var3       Var4

-----           -----           -------         --------      --------

1                1234       2345          2222

2                1233       1111

3                11111      5555555    4444        6666

Super User
Posts: 10,787

## Re: Splitting Text into different variables/columns

```Plz don't bring it up, it is almost five year old.
Start a brand new session.

data temp;
infile cards dlm=',';
input PK var : \$2000.;
cards;
1,1234|2345|2222
2,1233|1111
3,11111|5555555|4444|6666|
;
run;
proc sql noprint;
select max(countw(var,'|')) into : n from temp;
quit;
data want;
set temp;
array var_{&n} \$ 40;
do i=1 to countw(var,'|');
var_{i}=scan(var,i,'|');
end;
drop i ;
run;

```
☑ This topic is solved.

Discussion stats
• 5 replies
• 6223 views
• 1 like
• 4 in conversation