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

Splitting Text into different variables/columns

Ronein — Sun, 23 Sep 2018 09:54:56 GMT

Hello

Here is a code to split a char column to multiple columns.

In this code I know in advance that there are maximum 4 words in the string.

My question is How can I modify the code that it will calculate automatically the maximum number of words.

data cars_parse;
 set sashelp.cars;
 array modelname[5] $15 model1-model5;
 do i = 1 to 5;
  modelname[i] = scan(model,i,", ");
 end;
 keep model model1-model5;
run;

Re: Splitting Text into different variables/columns

Shmuel — Sun, 23 Sep 2018 09:55:06 GMT

Use function COUNTW()

Re: Splitting Text into different variables/columns

Ronein — Sun, 23 Sep 2018 10:04:39 GMT

Thanks.

Will it work well now or we can do it with one step only?

data tbl1;
 set sashelp.cars;
delims = ' ,.!';                 /* delimiters: space, comma, period, ... */
numWords = countw(str, delims);       /* for each line of text, how many words? */
Run;


Proc SQL noprint;
select  max(numWords)  into   :No_words
 from tbl1;  
quit;
 

Data desired;
Set sashelp.cars;
 array modelname[&No_words.] $15 model1-model& No_words.;
 do i = 1 to & No_words.;
  modelname[i] = scan(model,i,", ");
 end;
 keep model model1-model& No_words.;
run;

Re: Splitting Text into different variables/columns

Shmuel — Sun, 23 Sep 2018 11:46:43 GMT

you can combine the first two steps in one data step:

data tbl1;
 set sashelp.cars  end=eof;
       retain numWords 0;
       delims = ' ,.!';                 /* delimiters: space, comma, period, ... */
       numWords = max(numWords , countw(str, delims));    
                            /* for each line of text, how many words? */
       if eof then call symput('No_words', left(numWords));
Run;