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
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:;
Hi Steelers,
Thanks for the solution! I will give it a try and let you know how it works!
Zhuo
Added delimiter for field:
ID01 FirstName LastName Element1Del1Element2Del2Element3
to
ID01 FirstName LastName Element1
ID01 FirstName LastName Element2
ID01 FirstName LastName Element3
data prep;
set have;
do i=1 to countw(Element3,'|');
var=scan(Element3,i,'|');
output;
end;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.