Splitting Text into different variables/columns

[ren2010]

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

Please help.

[art297 · Accepted solution]

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;

[ren2010]

Thank You!

[Ksharp]

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

[hellind]

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

[Ksharp]

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;