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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.