Splitting values in a variable

[r3570]

Hi All,

I have a dataset as below with variables A, B, C, D

A          B         C          D

XYZ    1,2,3   PQR    YZA

I need output as below

A          B         C          D

XYZ     1      PQR        YZA

XYZ     2      PQR        YZA

XYZ     3      PQR        YZA

Kindly help

[Patrick · Accepted solution]

Here one way to go.

data have;
  input (A B C D) ($);
  datalines;
XYZ 1,2,3 PQR YZA
;

data want(drop=_:);
  set have;
  _stop=sum(countc(b,','),1);
  _b=b;
  do _i=1 to _stop;
    b=scan(_b,_i);
    output;
  end;
run;

[maguiremq]

Also, here's another way to do it.

data have;
  input (A B C D) ($);
  datalines;
XYZ 1,2,3 PQR YZA
;

data want (drop = bb i);
	retain a b c d;
	set have (rename = (b = bb));
	do i = 1 to countw(bb, ",");
		b = scan(bb, i, ",");
		output;
	end;
run;

a b c d 
XYZ 1 PQR YZA 
XYZ 2 PQR YZA 
XYZ 3 PQR YZA