converting comma separated cells into rows

[podarum]

I have some rows that are separated by commas, eg ROW1 :Wells (column1) / D2E,D3R,D5H,H6J (column2) and ROW2 : Dons (coulmn1) / H5N,J9T, F4S (columns=2) and I want each value in the commas to be separated as such :

So from this:

Wells   D2E,D3R,D5H,H6J

Dons    H5N,J9T,F4S

to this:

Wells D2E

Wells D3R

Wells D5H

Wells H6J

Dons H5N

Dons J9T

Dons F4S

Thank you

[art297]

I think you are asking how to accomplish the following:

data have;

  informat name $10.;

  informat invalue $20.;

  input name invalue @;

  cards;

Wells   D2E,D3R,D5H,H6J

Dons    H5N,J9T,F4S

;

data want (keep=name value);

  set have;

  length value $3;

  i=1;

  do while (scan(invalue,i,",") ne "");

    value=scan(invalue,i,",");

    i+1;

    output;

  end;

run;

[FriedEgg]

data foo;

infile cards dlm='|';

input (name data) (:$20.);

do i=1 to count(data,',')+1;

  column2=scan(data,i,',');

  output;

end;

keep name column2;

cards;

Wells|D2E,D3R,D5H,H6J

Dons|H5N,J9T,F4S

;

run;

[podarum]

Great help guys, thanks...

[podarum]

Sorry guys, I have something I wasn't aware of (I forgot to express it), and don't know how to fix it:

Wells   D2E,D3R,D5H,H6J

Dons    H5N,J9T,F4S

Gens   L2G 7H9

Leads  K9P 5F9

to this:

Wells D2E

Wells D3R

Wells D5H

Wells H6J

Dons H5N

Dons J9T

Dons F4S

Gens L2G

Leads K9P

right now I get

Wells D2E

Wells D3R

Wells D5H

Wells H6J

Dons H5N

Dons J9T

Dons F4S

Gens L2G

Gens 7H9  (no need)

Leads K9P

Leads 5F9 (no need)

[art297]

Then you didn't use the code I had proposed as, if your values are always three characters, it would have eliminated the unwanted entries.

[FriedEgg]

I agree with Art, to get the result you say you now retrieve you would have to be using code other than that which we specified.

data foo;

infile cards dlm='|';

input (name data) (:$20.);

do i=1 to count(data,',')+1;

  column2=scan(data,i); *change made here to remove specified delimiter, it will still produce the same result but now properly scans instead of truncating to length;

  output;

end;

keep name column2;

cards;

Wells|D2E,D3R,D5H,H6J

Dons|H5N,J9T,F4S

Gens|L2G 7H9

Leads|K9P 5F9

;

run;

Wells D2E

Wells D3R

Wells D5H

Wells H6J

Dons H5N

Dons J9T

Dons F4S

Gens L2G

Leads K9P