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:
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;
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;
Thank you, RW9! It is straightforward to you, but not for me as I am just studying 'proc transpose'.
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:
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.
@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
@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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.