How to divide a dataset into columns?

[Lacona]

Hey all,

I have this dataset:

Beob.	score	author
1	58.2	Clancy
2	73.4	Clancy
3	73.1	Clancy
4	64.4	Clancy
5	72.7	Clancy
6	89.2	Clancy
7	43.9	Clancy
8	76.3	Clancy
9	76.4	Clancy
10	78.9	Clancy
11	69.4	Clancy
12	72.9	Clancy
13	85.3	Rowling
14	84.3	Rowling
15	79.5	Rowling
16	82.5	Rowling
17	80.2	Rowling
18	84.6	Rowling
19	79.2	Rowling
20	70.9	Rowling
21	78.6	Rowling
22	86.2	Rowling
23	74	Rowling
24	83.7	Rowling
25	69.4	Tolstoy
26	64.2	Tolstoy
27	71.4	Tolstoy
28	71.6	Tolstoy
29	68.5	Tolstoy
30	51.9	Tolstoy
31	72.2	Tolstoy
32	74.4	Tolstoy
33	52.8	Tolstoy
34	58.4	Tolstoy
35	64.5	Tolstoy
36	73.6	Tolstoy

I have to divide it into three columns, one Clancy, one Rowling and one Tolstoy, with the corresponding scores underneath each Author.

How do I do that?

Thank you!

[novinosrin · Accepted solution]

Hi @Lacona 

data have;
input Beob	score	author $;
cards;
1	58.2	Clancy
2	73.4	Clancy
3	73.1	Clancy
4	64.4	Clancy
5	72.7	Clancy
6	89.2	Clancy
7	43.9	Clancy
8	76.3	Clancy
9	76.4	Clancy
10	78.9	Clancy
11	69.4	Clancy
12	72.9	Clancy
13	85.3	Rowling
14	84.3	Rowling
15	79.5	Rowling
16	82.5	Rowling
17	80.2	Rowling
18	84.6	Rowling
19	79.2	Rowling
20	70.9	Rowling
21	78.6	Rowling
22	86.2	Rowling
23	74	Rowling
24	83.7	Rowling
25	69.4	Tolstoy
26	64.2	Tolstoy
27	71.4	Tolstoy
28	71.6	Tolstoy
29	68.5	Tolstoy
30	51.9	Tolstoy
31	72.2	Tolstoy
32	74.4	Tolstoy
33	52.8	Tolstoy
34	58.4	Tolstoy
35	64.5	Tolstoy
36	73.6	Tolstoy
;

proc transpose data=have out=temp;
by author;
var score;
run;
proc transpose data=temp out=want(drop=_name_);
by _name_;
var col:;
id author;
run;

[Lacona]

With proc transpose, my data looks like this:

Clancy       score.    score.    score
Rowling     score.   score.    score
Tolstoy      score.   score.    score

But I want my new dataset look like this:

Clancy   Rowling   Tolstoy

score.    score.      score

score.    score.      score

score.    score.      score 

...

[novinosrin]

Did you test/run the double transpose code?

[Lacona]

My foult! Somehow I ran just the first transpose data.
Now I tried again and it works perfectly! 

Thank you so much!

[Reeza]

How does Beob come into play? Is it relevant? If not, do you have any other values to identify your new rows?

[Tom]

Is there any variable that sets the order of the results? Like a date variable?

Otherwise how do you know which values of CLANCY should be paired with which values of ROWLING?

[ballardw]

If by any chance you wanted a report that people read and maintains the order of the scores:

data have;
input Beob	score	author $;
cards;
1	58.2	Clancy
2	73.4	Clancy
3	73.1	Clancy
4	64.4	Clancy
5	72.7	Clancy
6	89.2	Clancy
7	43.9	Clancy
8	76.3	Clancy
9	76.4	Clancy
10	78.9	Clancy
11	69.4	Clancy
12	72.9	Clancy
13	85.3	Rowling
14	84.3	Rowling
15	79.5	Rowling
16	82.5	Rowling
17	80.2	Rowling
18	84.6	Rowling
19	79.2	Rowling
20	70.9	Rowling
21	78.6	Rowling
22	86.2	Rowling
23	74	Rowling
24	83.7	Rowling
25	69.4	Tolstoy
26	64.2	Tolstoy
27	71.4	Tolstoy
28	71.6	Tolstoy
29	68.5	Tolstoy
30	51.9	Tolstoy
31	72.2	Tolstoy
32	74.4	Tolstoy
33	52.8	Tolstoy
34	58.4	Tolstoy
35	64.5	Tolstoy
36	73.6	Tolstoy
;

data temp;
   set have;
   by notsorted author;
   if first.author then count=0;
   count+1;
run;

proc report data= temp;
   column count author,score;
   define count /group noprint;
   define author/across;
   define score / '';
run;

[Ksharp]

data have;
input Beob	score	author $;
cards;
1	58.2	Clancy
2	73.4	Clancy
3	73.1	Clancy
4	64.4	Clancy
5	72.7	Clancy
6	89.2	Clancy
7	43.9	Clancy
8	76.3	Clancy
9	76.4	Clancy
10	78.9	Clancy
11	69.4	Clancy
12	72.9	Clancy
13	85.3	Rowling
14	84.3	Rowling
15	79.5	Rowling
16	82.5	Rowling
17	80.2	Rowling
18	84.6	Rowling
19	79.2	Rowling
20	70.9	Rowling
21	78.6	Rowling
22	86.2	Rowling
23	74	Rowling
24	83.7	Rowling
25	69.4	Tolstoy
26	64.2	Tolstoy
27	71.4	Tolstoy
28	71.6	Tolstoy
29	68.5	Tolstoy
30	51.9	Tolstoy
31	72.2	Tolstoy
32	74.4	Tolstoy
33	52.8	Tolstoy
34	58.4	Tolstoy
35	64.5	Tolstoy
36	73.6	Tolstoy
;

data temp(index=(count));
   set have;
   by notsorted author;
   if first.author then count=0;
   count+1;
run;

proc transpose data=temp out=want(drop=_:);
by count;
id author;
var score;
run;