topic Re: Single column to multiple column of comma separated in SAS Programming

Single column to multiple column of comma separated

kundamn0 — Sun, 26 Feb 2017 11:08:30 GMT

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,

Re: Single column to multiple column of comma separated

Yavuz — Sun, 26 Feb 2017 11:16:50 GMT

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;

Re: Single column to multiple column of comma separated

Ksharp — Sun, 26 Feb 2017 11:18:55 GMT

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;

Re: Single column to multiple column of comma separated

Kurt_Bremser — Sun, 26 Feb 2017 15:55:06 GMT

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

(I'd prefer the second format)

Re: Single column to multiple column of comma separated

SASComm1 — Fri, 15 Oct 2021 23:01:23 GMT

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

Re: Single column to multiple column of comma separated

Patrick — Sat, 16 Oct 2021 00:03:20 GMT

@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;