topic Re: Transpose in SAS Programming

Transpose

Barney1998 — Wed, 31 May 2023 13:01:53 GMT

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?

Re: Transpose

PaigeMiller — Wed, 31 May 2023 13:12:03 GMT

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

Re: Transpose

FreelanceReinh — Wed, 31 May 2023 13:35:51 GMT

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.

Re: Transpose

ballardw — Wed, 31 May 2023 15:23:35 GMT

@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.

Re: Transpose

Barney1998 — Thu, 01 Jun 2023 10:45:08 GMT

Thank you so much!