topic Re: split one string into multiple row in SAS Enterprise Guide

split one string into multiple row

Rita_Viegas — Fri, 14 Jun 2019 15:21:21 GMT

Dear all,

I am having trouble using the scan function.

I am trying to split the information separated by comma in a column, just like the example:

User Funcoes

12345 A,B,C,D,,E,F,G,...

What I want is something like

User Funcoes

12345 A

12345 B

12345 C

12345 D

12345 E

12345 F

12345 G

12345 ...

data work.teste;
   set WORK.TESTE;
   Funcoes= teste;
   do i= 1 to (countw(Funcoes));
      teste = scan(Funcoes, i,',');
      output;
   end;
run;

I am trying this code but it is not returning any values.

Can you help me?

Thank you in advance,

Rita

Re: split one string into multiple row

Tom — Fri, 14 Jun 2019 15:24:01 GMT

Note that you are overwriting your input. That could cause trouble.

Describe more what is happening. Are you getting errors in the log?

Re: split one string into multiple row

Rita_Viegas — Fri, 14 Jun 2019 15:27:27 GMT

Hello,

I am not getting any error.

The output data does not returning anything. Just the name of the columns.

Probably my program is not correct. I am not an expert user 🙂

Thank you,

Rita

Re: split one string into multiple row

novinosrin — Fri, 14 Jun 2019 15:40:43 GMT

Hello @Rita_Viegas


data test;
input User        Funcoes :$30.;
cards;
12345       A,B,C,D,,E,F,G
;



data youwant_this;
   set test;
/*   Funcoes= teste;this is wrong */
   do i= 1 to (countw(Funcoes,','));
      teste = scan(Funcoes, i,',');
      output;
   end;
run;

data or_you_want_this;
   set test;
/*   Funcoes= teste;this is wrong*/
   do i= 1 to (countw(Funcoes,',','m'));
      teste = scan(Funcoes, i,',','m');
      output;
   end;
run;

Re: split one string into multiple row

ballardw — Fri, 14 Jun 2019 15:41:10 GMT

Provide example data is the form of a data step and paste into a code box opened with the forum's {I} or "running man" icon.

data have;
   infile datalines dlm=' ';
   input user $ funcoes $15.;
datalines;
12345 A,B,C,D,,E,F,G
;

data want;
   set have;
   length teste $ 1;
   do i= 1 to (countw(Funcoes));
      teste = scan(Funcoes, i,',');
      if not missing(teste) then output;
   end;
   drop I funcoes;
run;

The length for TESTE should be longest value expect to extract from the FUNCOES string.

Since your example data had a missing value between D and E that did not appear in the desired output I added a test for a non-missing TESTE value.

Re: split one string into multiple row

Reeza — Fri, 14 Jun 2019 15:43:10 GMT

Your code looks correct. But, can you open up your original data set and make sure it's still valid? Your style of coding (same data set input and output names) can destroy the original data set and makes it really hard to debug issues.

Try recreating it, and then running this version and posting the log back.

data work.teste2;
   set WORK.TESTE;
   Funcoes= teste;
   do i= 1 to (countw(Funcoes));
      teste = scan(Funcoes, i,',');
      output;
   end;
run;

@Rita_Viegas wrote:

Hello,

I am not getting any error.

The output data does not returning anything. Just the name of the columns.

Probably my program is not correct. I am not an expert user 🙂

Thank you,

Rita

Re: split one string into multiple row

novinosrin — Fri, 14 Jun 2019 15:52:57 GMT

Hi @Reeza You should start drinking coffee as I have always recommended Starbucks as opposed to Canada's favorite Tim hortons lol.

Funcoes= teste; causes Funcoes to be set to missing as teste is an assignment that is reset to missing at the beginning of each iteration of the datastep. So I commented that part.

Re: split one string into multiple row

Reeza — Fri, 14 Jun 2019 15:57:18 GMT

@novinosrin wrote:

Hi @Reeza You should start drinking coffee as I have always recommended Starbucks as opposed to Canada's favorite Tim hortons lol.

Funcoes= teste; causes Funcoes to be set to missing as teste is an assignment that is reset to missing at the beginning of each iteration of the datastep. So I commented that part.

@novinosrin I don't drink any coffee -- maybe that's the problem 😉 Actually Starbucks Hibiscus tea (well caffeinated) is currently my favourite drink!

Reading the code, my assumption is that there's a variable in the data set called teste that the users is temporarily renaming to funcoes so that the output variable can be reset. Though that could still cause issues.

Re: split one string into multiple row

novinosrin — Fri, 14 Jun 2019 15:58:45 GMT

"Reading the code, my assumption is that there's a variable in the data set called teste that the users is temporarily renaming to funcoes so that the output variable can be reset. Though that could still cause issues."

Oh well, that's super diligence. You beat me big time

Re: split one string into multiple row

Reeza — Fri, 14 Jun 2019 16:07:34 GMT

It's not necessarily right though - only the OP knows 🙂

Re: split one string into multiple row

FreelanceReinh — Fri, 14 Jun 2019 16:14:25 GMT

I suspect that the OP saw a similar assignment statement in an old program where it was meant to create a new character variable with the same length as the original variable. Of course, the correct implementation of this technique would have been teste=Funcoes;.

However, with the current release of SAS 9.4 this is redundant because the behavior of the SCAN function has changed so that the default length of teste is that of Funcoes (and not 200 in most cases).