split the value in the same cell to different cells but in the same column

[Alexxxxxxx]

Hello,

I am trying to split the value in the same cell to different cells but in the same column.

For example, for table 1

No	Code
1	XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB
2	NYSE:IAB; DB:3NL

I expect to have table 2 

No	Code
1	XTRA:TLB
1	OTCPK:TLMO.F
1	LSE:0R0N
1	WBAG:TLB
1	DB:TLB
2	NYSE:IAB
2	DB:3NL

the strings in table 1 are separated by ";", however, I do not know how to convert them as table 2.

Could you please give me some suggestion about this? Thanks in advance.

data table1;
infile cards dlm=,;
input 
No: $2.	
Code: $20.;
cards;
"1","XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB"
"2","NYSE:IAB; DB:3NL"
;
run;

[jeppesj]

You can loop over all substrings in your "code" variable separated by semicolon using the countw and scan functions:

data have;
infile datalines delimiter=',';
length no $2 code $200;
input no $ code $;
datalines4;
1, XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB
2, NYSE:IAB; DB:3NL
;;;;
run;

data want(keep=no code);
   set have(rename = code = input_code);
   do i = 1 to countw(input_code,";");
      code = left(scan(input_code,i,";"));
      output;
   end;
run;

[ballardw]

---

@jeppesj wrote:

You can loop over all substrings in your "code" variable separated by semicolon using the count and scan functions:

data have;
infile datalines delimiter=',';
length no $2 code $200;
input no $ code $;
datalines4;
1, XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB;
2, NYSE:IAB; DB:3NL;
;;;;
run;

data want(keep=no code);
   set have(rename = code = input_code);
   do i = 1 to count(input_code,";");
      code = left(scan(input_code,i,";"));
      output;
   end;
run;

---

I think you meant COUNTW instead of COUNT.

[jeppesj]

I think you meant COUNTW instead of COUNT.

I do, and I have edited my response to reflect this. I had first by mistake included a semicolon at the end of each "code" in the input dataset.

[Shmuel · Accepted solution]

Your code results in syntax error and would not work.

You may run next cpde

data table1;
infile cards truncover;
input 
     var1 $4.	
     var2 $200.
 ;
    length Code $20;
    No = compress(var1,'",');
    var2 = compress(var2,'"');
    do i=1 to countw(var2,';');
       Code = scan(var2,i,';');
       output;
    end;
    keep No Code;
cards4;
"1","XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB"
"2","NYSE:IAB; DB:3NL"
;;;;
run;

or alternatively change your input rows as in next code:

data table1;
infile cards truncover;
input 
     No 	
     var2 $200.
 ;
    length Code $20;
    do i=1 to countw(var2,';');
       Code = scan(var2,i,';');
       output;
    end;
    keep No Code;
cards4;
1 XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB
2 NYSE:IAB; DB:3NL
;;;;
run;

[Kurt_Bremser]

You can simplify the first code by using DLM= and DSD:

data table1;
infile cards dlm="," dsd truncover;
input 
  no :$4. 
  var2 :$200.
;
length Code $20;
do i = 1 to countw(var2,';');
  Code = scan(var2,i,';');
  output;
end;
keep No Code;
cards4;
"1","XTRA:TLB; OTCPK:TLMO.F; LSE:0R0N; WBAG:TLB; DB:TLB"
"2","NYSE:IAB; DB:3NL"
;;;;