DATA Step, Macro, Functions and more

Reg:Splitting obs based on comma

Reply
Contributor
Posts: 37

Reg:Splitting obs based on comma

Data temp;

infile cards dlm="|";

input bcode$ Rcode $;

cards;

tcs,satyam|123,456

|

rnrl|567

run;

actually if bode is having two obs separated by comma it shoukld go in to two rows and rcode is having two obs separated by , should go to 2 rows

if both are balank it should have single rom  and if it is having only one obs it should be as it is.

output

bcode Rcode

tcs      null

satam  null

    null      123

   null      456

<only one balnk row shuld come as both are blank for both the values>      

rnrl        null

  null        567

Valued Guide
Posts: 2,174

Reg:Splitting obs based on comma

Example shows no more than two comma-separated pairs, but that might expand.

Example shows no more than pipe mark(|), but that might expand.

This first code delivers no more than requested;

data want( keep= bcode rcode );

   length bcode rcode $8;

   input;

   if _infile_ = '|' or _infile_ = ' ' then

      do;

         output;

         delete;

      end;

   p1 = scan( _infile_, 1, '|' );

   p2 = scan( _infile_, 2, '|' );

   pcode = ' ';

   bcode = scan( p1,1,',' );

   output;

   bcode = scan( p1,2,',' );

   if bcode ne ' ' then

      do;

         output;

         bcode = ' ';

      end;

   rcode = scan( p2, 1, ',' );

   output;

   rcode = scan( p2, 2, ',' );

   if rcode ne ' ' then

      output;

list;cards;

tcs,satyam|123,456

|

rnrl|567

;

SAS Employee
Posts: 104

Reg:Splitting obs based on comma

Try this:

 
Data temp;
   infile datalines dlm="|" truncover dsd;
   input _b:$20. _r:$20.;
   if _infile_='|' then output;
   else do _i=1 to 2;
      bcode=scan(_b,_i,',');
      if not missing(bcode) then output;
      call missing(bcode);
      rcode=scan(_r,_i,',');
      if not missing(rcode) then output;
      call missing(rcode);
   end; 
   drop _:;
datalines;
tcs,satyam|123,456
|
rnrl|567
run;

Ask a Question
Discussion stats
  • 2 replies
  • 133 views
  • 0 likes
  • 3 in conversation