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

[Anurag_Kumar]

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

[PaigeMiller]

Remove OUTPUT; from your code.

[Anurag_Kumar]

Removed Output, still no difference.

[novinosrin]

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!

[PaigeMiller]

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.

[PaigeMiller]

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;

[ballardw]

---

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