topic converting comma separated cells into rows in SAS Procedures

converting comma separated cells into rows

podarum — Tue, 18 Oct 2011 15:53:21 GMT

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

converting comma separated cells into rows

art297 — Tue, 18 Oct 2011 16:00:26 GMT

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;

converting comma separated cells into rows

FriedEgg — Tue, 18 Oct 2011 16:31:21 GMT

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;

converting comma separated cells into rows

podarum — Tue, 18 Oct 2011 17:44:37 GMT

Great help guys, thanks...

converting comma separated cells into rows

podarum — Tue, 18 Oct 2011 20:47:08 GMT

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)

converting comma separated cells into rows

art297 — Tue, 18 Oct 2011 21:18:52 GMT

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.

converting comma separated cells into rows

FriedEgg — Tue, 18 Oct 2011 22:00:41 GMT

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