topic Splitting values in a variable in SAS Programming

Splitting values in a variable

r3570 — Wed, 22 Sep 2021 09:17:09 GMT

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

Re: Splitting values in a variable

Patrick — Wed, 22 Sep 2021 09:24:19 GMT

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;

Re: Splitting values in a variable

maguiremq — Wed, 22 Sep 2021 12:08:25 GMT

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