BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Alexxxxxxx
Pyrite | Level 9

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

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

 

I expect to have table 2 

NoCode
1XTRA:TLB
1OTCPK:TLMO.F
1LSE:0R0N
1WBAG:TLB
1DB:TLB
2NYSE:IAB
2DB: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;
1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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;

 

View solution in original post

5 REPLIES 5
jeppesj
Calcite | Level 5

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
Super User

@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
Calcite | Level 5

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
Garnet | Level 18

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
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 649 views
  • 2 likes
  • 5 in conversation