How to transpose data like this?

[MikeTurner]

I want to transpose the following data:

a b 1 x1 y1 x2 y2

c d 2 x3 y3 x4 y4;

Into:

a b 1 x1 y1

a b 1 x2 y2

c d 2 x3 y3

c d 2 x4 y4;

How to program?

Thanks!

[Ksharp · Accepted solution]

These data are in TXT file ?

data want;
infile datalines dlm=' ' truncover;
input a $ b $ c $ d $ e $ @;
do until( missing(d)); 
 output;
 input d $ e $ @; 
end;
input;
datalines;
a b 1 x1 y1 x2 y2
c d 2 x3 y3 x4 y4
;
run;

Ksharp

[MikeTurner]

Thanks. But if the original data are in one datset, how to program it/

[Ksharp]

You have Five variables or Only one variable to hold a record?

[MikeTurner]

Thanks.

I have seven variables in my original dataset. Now I want to hold a record (5 variables) using the first 3 variables.

[Ksharp]

OK.

data have;
input (a1 - a7) (: $40.);
datalines;
a b 1 x1 y1 x2 y2
c d 2 x3 y3 x4 y4
;
run;
data want(drop=a4-a7);
 set have;
 x=a4;y=a5;output;
 x=a6;y=a7;output;
run;
  

Ksharp

[Ksharp]

If you have only one variable.

data have;
infile datalines length=len;
input a $varying200. len;
datalines;
a b 1 x1 y1 x2 y2
c d 2 x3 y3 x4 y4
;
run;
data want(keep=xx);
 set have;
 length x xx $ 400;
 i=4;
 x=catx(' ',scan(a,1),scan(a,2),scan(a,3));
 xx=catx(' ',x,scan(a,i),scan(a,i+1));
 do until(missing(scan(a,i)));
 output;
 i+2;
 xx=catx(' ',x,scan(a,i),scan(a,i+1));
 end;
run;

 

Ksharp