split one string into multiple row

[Rita_Viegas]

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

[Tom]

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

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

[Rita_Viegas]

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

[novinosrin]

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;

[ballardw]

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.

[Reeza]

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

---

[novinosrin]

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. 

[Reeza]

---

@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.

[novinosrin]

"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

[Reeza]

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

[FreelanceReinh]

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).