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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.