topic Re: proc transpose replicating rows in SAS Programming

proc transpose replicating rows

dustychair — Fri, 05 Aug 2022 17:43:55 GMT

Hi all,

I have data set such as below. I am using proc transpose to put items in the columns and students in the rows and there will be item scores where items and students match. I am not getting any error but i am getting a data set below. Here is my code. I have no idea why this is happening.Thank you

Have:

Studentid itemscore itemid

00s1 1 it1

00s1 1 it2

00s1 0 it3

00s1 2 it4

00a2 0 it1

00a2 1 it2

00a2 3 it3

Getting:

it1 it2 it3 it4

00s1 1

00s1 0

00s1 2

00a2 0

00a2 1

00s2 3

proc sort data=Cleanest_mat04;
by     StudentID ;
run;
data temp;
set Cleanest_mat04;
by    StudentID   ;
if first.StudentID then group=1;
else group+1;
run;
proc sort data=temp;
by     StudentID group;
run;

Proc Transpose data = temp out= tr_fully_mat04_n  ;
var itemscore;
by    StudentID group;
id itemid;
run;

Re: proc transpose replicating rows

ballardw — Fri, 05 Aug 2022 19:25:35 GMT

It might help to show what you expect the output to be.

Hint: place an actual . for "missing values".

The forum software will reformat text pasted into the message window and likely your "getting" data set example is intended to show missing values but since I have to believe your source had spaces the result after the forum finished with the pasted text is not very clear.

Also, if you open a text box using the </> icon that appears above the message window to paste text the window doesn't reformat the text.

Your result does not include the GROUP variable and that variable on the By statement in Proc Transpose means that output rows are studentid group combinations. The example input means that there is only one Score for each group.

I think that you do not want the GROUP variable on the BY statement in Proc Transpose.

See if this does what you expect:

data have;
   input Studentid $ itemscore itemid $;
datalines;
00s1 1 it1
00s1 1 it2
00s1 0 it3
00s1 2 it4
00a2 0 it1
00a2 1 it2
00a2 3 it3
;
proc sort data=have;
   by studentid itemid;
run;
data temp;
   set have;
   by StudentId;
   if first.studentid then group=1;
   else group+1;
run;
proc sort data=temp;
   by studentid group;
run;

proc transpose data=temp out=trans;
   by studentid ;
   var itemscore;
   id itemid;
run;

Please note the DATA step to provide example data and the appearance of the text box around the code.

Re: proc transpose replicating rows

dustychair — Fri, 05 Aug 2022 18:41:14 GMT

Oh my Goodness! deleting group helped. Thank you!