topic Re: How to split a column from a text into multiple columns? in SAS Data Management

How to split a column from a text into multiple columns?

leglucker — Fri, 15 Jan 2021 08:11:20 GMT

Hi, How can I split this text into multiple columns:

"TEST","N/A","Week One","Disabled","Disabled","Disabled","Disabled"
"TEST","N/A","MON, TUE, WED, THU, FRI","Disabled","Disabled","Disabled","Disabled"
"TEST","N/A","Week One","Disabled","Disabled","Disabled","Disabled"
"TEST","N/A","Stop On Battery Mode, No Start On Batteries","Disabled","Disabled","Disabled","Disabled"

The result in this example should be 7 columns

TEST	N/A	Week One	Disabled	Disabled	Disabled	Disabled
TEST	N/A	MON, TUE, WED, THU, FRI	Disabled	Disabled	Disabled	Disabled
TEST	N/A	Week One	Disabled	Disabled	Disabled	Disabled
TEST	N/A	Stop On Battery Mode, No Start On Batteries	Disabled	Disabled	Disabled	Disabled

Re: How to split a column from a text into multiple columns?

PeterClemmensen — Fri, 15 Jan 2021 08:12:49 GMT

So this text is in a single variable now? Or is this in a separate file?

Re: How to split a column from a text into multiple columns?

leglucker — Fri, 15 Jan 2021 08:18:39 GMT

This text is in a sigle rows and 1 row looks like:
"TEST","N/A","Stop On Battery Mode, No Start On Batteries","Disabled","Disabled","Disabled","Disabled"

Re: How to split a column from a text into multiple columns?

PeterClemmensen — Fri, 15 Jan 2021 08:40:39 GMT

In a SAS data set, correct? Not an external file like csv?

Re: How to split a column from a text into multiple columns?

leglucker — Fri, 15 Jan 2021 08:50:53 GMT

Sas Data looks like

Re: How to split a column from a text into multiple columns?

Kurt_Bremser — Fri, 15 Jan 2021 09:37:10 GMT

From where does that data originate? This clearly looks like a line from a CSV file, so it should be read initially as such.

Re: How to split a column from a text into multiple columns?

leglucker — Fri, 15 Jan 2021 11:18:38 GMT

Data are from Win TaskSchedule
data have;
infile "schtasks /fo csv" pipe end=eof;
input ; var= _infile_;
run;

Re: How to split a column from a text into multiple columns?

Kurt_Bremser — Fri, 15 Jan 2021 15:50:14 GMT

Then you can do it right there:

data want;
infile "schtasks /fo csv" pipe dlm="," dsd end=eof;
input
  name :$10.
  var2 :$5.
  when :$40.
  var4 :$10.
  var5 :$10.
  var6 :$10.
  var7 :$10.
;
run;

Adapt variable names and formats as needed.