Separate string and convert it to rows

Zatere — Tue, 15 Aug 2023 12:25:40 GMT

Hello,

I would ask for help on the below please.

I have a dataset in the following format:

data have;
  INFILE DATALINES DELIMITER=',' DSD;
input Branch $ Employee Products $50.;
datalines;
A, 1, Ball, Paper, Pen, Glass, Car,
B, 1, Dog, Cat, Bird, Water, Pencil,
B, 2, Ball, Paper, Pen, Glass, Car,
A, 2, Dog, Cat, Bird, Water, Pencil,
run;

The column "Products" contains values separated by commas.

I want to create a new row with each of these values.

The new structure should be as follows:

data want;
input Branch $ Employee Products $50.;
datalines;
A 1 Ball
A 1 Paper
A 1 Pen
A 1 Glass
A 1 Car
B 1 Dog
B 1 Cat
B 1 Bird
B 1 Water
B 1 Pencil
A 2 Ball
A 2 Paper
A 2 Pen
A 2 Glass
A 2 Car
B 2 Dog
B 2 Cat
B 2 Bird
B 2 Water
B 2 Pencil
;
run;

Any help please.

Thanks.

Re: Separate string and convert it to rows

PaigeMiller — Tue, 15 Aug 2023 12:37:10 GMT

This is a pretty simple DO loop where you use the SCAN function to find the i-th "word", where "word" is delimited by a comma.

data want;
    set have;
    do i=1 to countw(products,',');
        product=scan(products,i,',');
        if not missing(product) then output;
    end;
    drop i products;
run;

topic Separate string and convert it to rows in SAS Programming

Separate string and convert it to rows

Re: Separate string and convert it to rows