topic Re: How to split a column based on delimiter with no fixed number of columns or width of phrases? in SAS Programming

How to split a column based on delimiter with no fixed number of columns or width of phrases?

Anurag_Kumar — Thu, 25 Apr 2019 13:35:42 GMT

I have a dataset with a variable animal type(example). There are 30 animals overall and any row might have any number of animals:

Big cat; Tiny strong dog; White pony

Big cat;

White pony; Small rabbit;

Tiny strong dog; White pony

I need to separate these into as many columns as required for a particular row. There is no restriction of keeping one column for one animal type. I am using the below code but there are multiple rows being generated along with multiple columns and these columns are empty.

data want;
set have;

array var(30);

do i=1 to 30;
var(i)=scan(Animal,i,";");
end;

run;

Please advise.

SAS EG version: 7.15

Re: How to split a column based on delimiter with no fixed number of columns or width of phrases?

PaigeMiller — Thu, 25 Apr 2019 13:36:24 GMT

Remove OUTPUT; from your code.

Re: How to split a column based on delimiter with no fixed number of columns or width of phrases?

Anurag_Kumar — Thu, 25 Apr 2019 13:42:48 GMT

Removed Output, still no difference.

Re: How to split a column based on delimiter with no fixed number of columns or width of phrases?

novinosrin — Thu, 25 Apr 2019 13:47:26 GMT

Hi @Anurag_Kumar Welcome to SAS communities as I see your tag as New contributor. Can you please post a sample of the OUTPUT you want for the input? Thanks!

Re: How to split a column based on delimiter with no fixed number of columns or width of phrases?

PaigeMiller — Thu, 25 Apr 2019 14:11:45 GMT

Perhaps you have read the data into your SAS data set improperly?

Also, are there ERRORs, WARNINGs or NOTEs in your SAS log? Please show us the ENTIRE SAS log.

At this point, we need the SAS code that reads this data, the LOG, and you also need to show us the desired output.

Re: How to split a column based on delimiter with no fixed number of columns or width of phrases?

PaigeMiller — Thu, 25 Apr 2019 14:16:01 GMT

Okay, I can get this to work, does this meet your needs?

data have;
    input animal &$200.;
    cards4;
    Big cat; Tiny strong dog; White pony
    Big cat; guinea pig
    White pony; Small rabbit;
    Tiny strong dog; White pony
    ;;;;
run; 

data want;
    set have;
    array var(30) $ 40;
    do i=1 to 30;
        var(i)=scan(animal,i,";");
    end;
run;

Re: How to split a column based on delimiter with no fixed number of columns or width of phrases?

ballardw — Thu, 25 Apr 2019 14:36:38 GMT

@Anurag_Kumar wrote:

I have a dataset with a variable animal type(example). There are 30 animals overall and any row might have any number of animals:

Big cat; Tiny strong dog; White pony

Big cat;

White pony; Small rabbit;

Tiny strong dog; White pony

I need to separate these into as many columns as required for a particular row. There is no restriction of keeping one column for one animal type. I am using the below code but there are multiple rows being generated along with multiple columns and these columns are empty.

data want;
set have;

array var(30); <= this defines an array of numeric values so of course a number cannot be "Big cat"

do i=1 to 30;
var(i)=scan(Animal,i,";");
end;

run;

Please advise.

SAS EG version: 7.15

Array var(30) $ 25; to create an array of character values able to hold up to 25 characters per value. Set the length as seems appropriate.