DATA Step, Macro, Functions and more

How do I duplicate rows with different element from a field

Reply
Contributor zz
Contributor
Posts: 28

How do I duplicate rows with different element from a field

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

Super User
Posts: 19,851

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

This is know as transposing data, and there's a proc for that Smiley Wink 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....
Valued Guide
Posts: 860

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

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

Contributor zz
Contributor
Posts: 28

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

Posted in reply to Steelers_In_DC

Hi Steelers,

 

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

 

Zhuo

Contributor zz
Contributor
Posts: 28

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

Added delimiter for field:

 

ID01  FirstName  LastName  Element1Del1Element2Del2Element3

 

to

 

ID01  FirstName  LastName  Element1

ID01  FirstName  LastName  Element2

ID01  FirstName  LastName  Element3

Super User
Posts: 10,041

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

data prep;
set have;

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

end;
run;

Ask a Question
Discussion stats
  • 5 replies
  • 233 views
  • 0 likes
  • 4 in conversation