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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.