## Transpose of complex table

Solved
Regular Contributor
Posts: 210

# Transpose of complex table

Hi all,

Could you please help in 'transposing of complex table'. Here below is the code of the table and two pirctures: 1- what it is and 2-how it should be transposed. Thank you!

``````data test;
length Patient \$1 Examination \$7 Visit Finding \$1;
infile datalines dlm='	';
input Patient Examination Visit Finding;
datalines;
1	SKIN	1	N
1	SKIN	2	N
1	SKIN	3	Y
1	ABDOMEN	1	N
1	ABDOMEN	2	N
1	ABDOMEN	3	Y
;
run;``````

1 - what I have:

2 - what I need:

Accepted Solutions
Solution
‎09-05-2017 07:59 AM
Super User
Posts: 9,397

## Re: Transpose of complex table

Seem very straightforward to me?

```data test;
length Patient \$1 Examination \$7 Visit Finding \$1;
infile datalines dlm='	';
input Patient Examination Visit Finding;
datalines;
1	SKIN	1	N
1	SKIN	2	N
1	SKIN	3	Y
1	ABDOMEN	1	N
1	ABDOMEN	2	N
1	ABDOMEN	3	Y
;
run;

proc sort data=test;
by patient examination;
run;

proc transpose data=test out=want prefix=visit;
by patient examination;
var finding;
id visit;
run;
```

All Replies
Solution
‎09-05-2017 07:59 AM
Super User
Posts: 9,397

## Re: Transpose of complex table

Seem very straightforward to me?

```data test;
length Patient \$1 Examination \$7 Visit Finding \$1;
infile datalines dlm='	';
input Patient Examination Visit Finding;
datalines;
1	SKIN	1	N
1	SKIN	2	N
1	SKIN	3	Y
1	ABDOMEN	1	N
1	ABDOMEN	2	N
1	ABDOMEN	3	Y
;
run;

proc sort data=test;
by patient examination;
run;

proc transpose data=test out=want prefix=visit;
by patient examination;
var finding;
id visit;
run;
```
Regular Contributor
Posts: 210

## Re: Transpose of complex table

[ Edited ]

Thank you, RW9! It is straightforward to you, but not for me as I am just studying 'proc transpose'.

Super User
Posts: 9,397

## Re: Transpose of complex table

Ah, simpest way to think about it is: one variable going up then proc transpose, multiple variables going up then arrays.  You can find quite a lot of information by searching reshaping data, for instance:

Super User
Posts: 13,293

## Re: Transpose of complex table

Is this new data set input to a model or other statistical analysis or only used to print a report?

If you are printing a report for people to read than one of the report procedures such as Proc Report or Tabulate may be of interest.

Regular Contributor
Posts: 210

## Re: Transpose of complex table

Hi ballardw, it is a step from sdtm to adam.
PROC Star
Posts: 8,145

## Re: Transpose of complex table

@DmytroYermak: While you already have your answer, @RW9's suggested code left off one thing if you want your resulting file to actually appear the way you showed it in your original post, namely with the transposed variables ending with the string 'Findings'.

You can easily do that by using PROC TRANSPOSE's suffix option. e.g.:

```proc transpose data=test out=want (drop=_:)
prefix=Visit
suffix=Findings;
by patient examination;
var finding;
id visit;
run;```

Art, CEO, AnalystFinder.com

Regular Contributor
Posts: 210

## Re: Transpose of complex table

Thank you, art297! I have used your recommendation. Just one quiestion: what does ":" in the option "drop" mean?
PROC Star
Posts: 8,145

## Re: Transpose of complex table

@DmytroYermak: The colon is a wild card. Thus drop=_: says drop any variable that begins with an underscore.

PROC TRANSPOSE automatically creates one or more system variables .. each beginning with an underscore.

Art, CEO, AnalystFinder.com

Regular Contributor
Posts: 210

## Re: Transpose of complex table

Thanks!
☑ This topic is solved.