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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.