Help using Base SAS procedures

Splitting Text into different variables/columns

Accepted Solution Solved
Reply
Contributor
Posts: 74
Accepted Solution

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

Please help.


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

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;

View solution in original post


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

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

Splitting Text into different variables/columns

Thank You!

Super User
Posts: 9,662

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: 90

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: 9,662

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.

Need further help from the community? Please ask a new question.

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