DATA Step, Macro, Functions and more

Transpose of complex table

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 142
Accepted Solution

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:

1.jpg

2 - what I need:

2.jpg


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

Re: Transpose of complex table

Posted in reply to DmytroYermak

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;

View solution in original post


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

Re: Transpose of complex table

Posted in reply to DmytroYermak

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;
Frequent Contributor
Posts: 142

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
Super User
Posts: 7,997

Re: Transpose of complex table

Posted in reply to DmytroYermak

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:

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&...

Super User
Posts: 11,343

Re: Transpose of complex table

Posted in reply to DmytroYermak

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.

Frequent Contributor
Posts: 142

Re: Transpose of complex table

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

Re: Transpose of complex table

Posted in reply to DmytroYermak

@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 

Frequent Contributor
Posts: 142

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: 7,492

Re: Transpose of complex table

Posted in reply to DmytroYermak

@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

 

Frequent Contributor
Posts: 142

Re: Transpose of complex table

Thanks!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 132 views
  • 1 like
  • 4 in conversation