How do I duplicate rows with different element from a field

[zz]

Hi, Could someone help me with the programming below please:

I need to format rows in a dataset by duplicating rows, from say

ID01  FirstName  LastName  Element1Element2Element3

to

ID01  FirstName  LastName  Element1

ID01  FirstName  LastName  Element2

ID01  FirstName  LastName  Element3

That is: Copy the row with fields, except the last; Parse out this field value and make each parsed out element as the field value of the duplicated rows.  How do I best go about it?

Thanks much in advance!

Zhuo

[Reeza]

This is know as transposing data, and there's a proc for that 😉 If Element1/2/3 are in one variable you may need the SCAN function.

PROC TRANSPOSE

PROC TRANSPOSE
https://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#n01rzqgzs8vq1bn10h1w...

Transposing via a data step or proc transpose
http://www.ats.ucla.edu/stat/sas/modules/widetolong_data.htm
http://www.ats.ucla.edu/stat/sas/modules/wtol_transpose.htm

A macro written by users:
http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/TASS/Tabachneck-FlipTranspose....

[Steelers_In_DC]

I got a little mixed up based on your description of input - output.  Let me know if this is the solution you are looking for, cheers:

data have;
infile cards dsd;
input ID01$  FirstName$  LastName$  Element1Element2Element3 $50.;
cards;
ID01,Mark,Johnson,test1|test2|test3
;

data prep(drop=Element1Element2Element3);
set have;
var1=scan(Element1Element2Element3,1,'|');
var2=scan(Element1Element2Element3,2,'|');
var3=scan(Element1Element2Element3,3,'|');
run;

proc transpose data=prep out=want(drop=_NAME_)prefix=Element;by ID01 firstname lastname;var var:;

[zz]

Hi Steelers,

Thanks for the solution!  I will give it a try and let you know how it works!

Zhuo

[zz]

Added delimiter for field:

ID01  FirstName  LastName  Element1Del1Element2Del2Element3

to

ID01  FirstName  LastName  Element1

ID01  FirstName  LastName  Element2

ID01  FirstName  LastName  Element3

[Ksharp]

data prep;
set have;

do i=1 to countw(Element3,'|');
var=scan(Element3,i,'|');
output;

end;
run;