topic Re: Splitting Text into different variables/columns in SAS Procedures

Splitting Text into different variables/columns

ren2010 — Thu, 01 Sep 2011 18:50:50 GMT

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.

Splitting Text into different variables/columns

art297 — Thu, 01 Sep 2011 18:58:54 GMT

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;

Splitting Text into different variables/columns

ren2010 — Thu, 01 Sep 2011 21:42:37 GMT

Thank You!

Splitting Text into different variables/columns

Ksharp — Fri, 02 Sep 2011 04:50:02 GMT

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

Re: Splitting Text into different variables/columns

hellind — Thu, 05 Jan 2017 01:43:19 GMT

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

Re: Splitting Text into different variables/columns

Ksharp — Thu, 05 Jan 2017 03:02:11 GMT

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;