Transpose

[Barney1998]

Hallo to everyone,

I have this problem to overcome. I have a data set which their information from some columns are repeated. For example like:
DATA Input; INPUT Col1 $ Col2 $ Col3 $ Col4 $ Col5 $ Col6 $ Col7;

DATALINES;

A X X S F 1  10

A X X S F 2 20

A X X S F 3 30

B Y R S Z 1 20

B Y R S Z 2 40 ;

 RUN;

and i want to hold all the info in one row like

DATA Input; INPUT Col1 $ Col2 $ Col3 $ Col4 $ 1 $ 2 $ 3;

DATALINES;

A X X S F  10 20 30

B Y R S Z  20 30  -

 RUN;

what should i do?

[PaigeMiller]

Please explain the logic (Step-by-step, leaving nothing out) behind this re-arranging of the data.

[FreelanceReinh · Accepted solution]

Hallo @Barney1998,

Try this:

data have;
input (col1-col5) ($) col6 col7;
datalines;
A X X S F 1 10
A X X S F 2 20
A X X S F 3 30
B Y R S Z 1 20
B Y R S Z 2 40
A B C D E 3 13
A B C D E 2 22
;

proc transpose data=have out=want(drop=_name_) prefix=somename;
by col1 col2 col3 col4 col5 notsorted; /* shorter: col1-col5 */
id col6;
var col7;
run;

If your real HAVE dataset is sorted (rather than only grouped) by COL1-COL5, you can omit the NOTSORTED keyword in the BY statement. COL6 may also be a character variable, but must not contain duplicate values within a BY group.

[Barney1998]

Thank you so much!

[ballardw]

---

@Barney1998 wrote:

Hallo to everyone,

I have this problem to overcome. I have a data set which their information from some columns are repeated. For example like:
DATA Input; INPUT Col1 $ Col2 $ Col3 $ Col4 $ Col5 $ Col6 $ Col7;

DATALINES;

A X X S F 1  10

A X X S F 2 20

A X X S F 3 30

B Y R S Z 1 20

B Y R S Z 2 40 ;

 RUN;

and i want to hold all the info in one row like

DATA Input; INPUT Col1 $ Col2 $ Col3 $ Col4 $ 1 $ 2 $ 3;

DATALINES;

A X X S F  10 20 30

B Y R S Z  20 30  -

 RUN;

what should i do?

---

Where does the 30 on the row starting with B in your Want data come from. There isn't any 30 in the starting data.