Transpose

[deleted_user]

Hello Everyone,

I am having trouble with finding the total score (tot_score) for the following data. My data has three variables (subject, visit, score) and it has the form:

100 1 60
100 2 62
100 3 62
100 4 65
100 5 70
101 1 58
101 2 59
101 3 62
101 4 63
101 5 65

I could transpose the data and get a final dataset with two obs (one for subject 100 and another for subject 101). I am not able to get any value for the newly introduced variable tot_score.

Also, I have one more question: If any of the visit is missing for any patient, what precaution I need to take?

Thanks and Regards
Sars

[deleted_user]

I would just use a PROC SQL

PROC SQL;
CREATE TABLE WORK.TOTALS AS
SELECT SUBJECT, SUM(SCORE) AS TOT_SCORE
FROM WORK.DATA
GROUP BY SUBJECT;

On how to deal with missing visits, it is really not a coding question, it is more a methdological protocol question. What is the protocol for dealing with missing scores/visits in your research? For example, some leave them out, some fill in with the average, some fill in with the extrapolated value. The point is, there is no real one way to deal with missing data, you have to have a protocol that is based on your research on what to do about them.

Ike Eisenhauer

[deleted_user]

Thanks for the response. I was thinking in terms of a retain statement, and got lost!!

Sars

[Cynthia_sas]

Hi:
In addition to PROC SQl, there are other methods of creating either reports or SAS datasets from your data:
[pre]

proc means data=visit sum n nway;
title '1) Proc Means Report';
class subject;
var score;
output out=mnout sum=totscore n=num_vis;
run;

proc tabulate data=visit f=comma8. out=work.tabout;
title '2) Proc Tabulate Report';
class subject visit;
var score;
table subject,
score * (sum='Tot Score' n='Num Visit')
/ rts=10;
run;

proc tabulate data=visit f=comma8.;
title '3) Proc Tabulate Report with Columns for IDs';
class subject visit;
var score;
table subject*
score * (sum='Tot Score' n='Num Visit')
/ rts=10;
run;

proc report data=visit nowd out=work.repout;
title '4) Proc Report Output';
column subject score n;
define subject / group 'Subject';
define score / sum 'Tot Score';
define n / 'Num Visit';
run;

proc print data=mnout;
title 'Data Set created by Proc Means #1';
run;

proc print data=tabout;
title 'Data Set created by Proc Tabulate #2';
run;

proc print data=repout;
title 'Data Set created by Proc Report #4';
run;
[/pre]

And you could use a DATA step program, as well. I'd consider this a how to summarize or create a summary data set problem more than a TRANSPOSE problem.

cynthia