topic How do I duplicate rows with different element from a field in SAS Programming

How do I duplicate rows with different element from a field

zz — Wed, 16 Dec 2015 19:50:54 GMT

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

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

Re: How do I duplicate rows with different element from a field

Reeza — Wed, 16 Dec 2015 19:59:29 GMT

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

Re: How do I duplicate rows with different element from a field

Steelers_In_DC — Wed, 16 Dec 2015 20:03:24 GMT

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:;

Re: How do I duplicate rows with different element from a field

zz — Wed, 16 Dec 2015 20:05:25 GMT

Added delimiter for field:

ID01 FirstName LastName Element1Del1Element2Del2Element3

ID01 FirstName LastName Element1

ID01 FirstName LastName Element2

ID01 FirstName LastName Element3

Re: How do I duplicate rows with different element from a field

zz — Wed, 16 Dec 2015 20:19:16 GMT

Hi Steelers,

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

Zhuo

Re: How do I duplicate rows with different element from a field

Ksharp — Thu, 17 Dec 2015 02:29:36 GMT

data prep;
set have;

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

end;
run;