Single column to multiple column of comma separated

[kundamn0]

Hey Experts,

I have a column data from my DB with a lot of values combined in a column. Each entry has multiple item ids separated by commas. I now need them in multiple columns. Any help will be appreciated.

Regards,

M 

[Yavuz]

You can google search for "sas csv import"
Also you can import file from sas "file - import csv file" section

PROC IMPORT DATAFILE="C:\test.csv"
OUT=SASCrunch
DBMS=csv
REPLACE;
GETNAMES=Yes;
RUN;

[Ksharp]

data have;
text='xx,ee,tt       ';output;
text='jj,ii,ii,kk';output;
run;
proc sql;
select max(countw(text,',')) into : max from have;
quit;
data want;
 set have;
 array x{&max} $ 100;
 do i=1 to countw(text,',');
  x{i}=scan(text,i,',');
 end;
 drop i;
run;

[Kurt_Bremser]

Do you need to split the values into multiple columns, or into a single column in multiple rows?

(I'd prefer the second format)

[SASComm1]

Much appreciated if one can show how to split the values into a single column in multiple rows.

[Patrick]

---

@SASComm1 wrote:

Much appreciated if one can show how to split the values into a single column in multiple rows.

---

See below @Ksharp's sample script repurposed for a single value in multiple rows.

data have;
  text='xx,ee,tt       ';
  output;
  text='jj,ii,ii,kk';
  output;
run;

data want;
  set have;

  do i=1 to countw(text,',');
    text2=scan(text,i,',');
    output;
  end;

  drop i;
run;