topic Re: split the value in the same cell to different cells but in the same column in SAS Programming

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

Alexxxxxxx — Wed, 22 Jul 2020 21:53:19 GMT

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;

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

jeppesj — Wed, 22 Jul 2020 22:56:54 GMT

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;

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

ballardw — Wed, 22 Jul 2020 22:32:11 GMT

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

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

jeppesj — Wed, 22 Jul 2020 22:43:11 GMT

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.

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

Shmuel — Wed, 22 Jul 2020 22:50:33 GMT

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;

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

Kurt_Bremser — Thu, 23 Jul 2020 09:35:59 GMT

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"
;;;;