topic How to divide a dataset into columns? in SAS Programming

How to divide a dataset into columns?

Lacona — Thu, 10 Oct 2019 17:01:55 GMT

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!

Re: How to divide a dataset into columns?

novinosrin — Thu, 10 Oct 2019 17:07:31 GMT

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;

Re: How to divide a dataset into columns?

Lacona — Thu, 10 Oct 2019 17:12:48 GMT

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

...

Re: How to divide a dataset into columns?

Reeza — Thu, 10 Oct 2019 17:15:03 GMT

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

Re: How to divide a dataset into columns?

novinosrin — Thu, 10 Oct 2019 17:33:32 GMT

Did you test/run the double transpose code?

Re: How to divide a dataset into columns?

Tom — Thu, 10 Oct 2019 19:35:44 GMT

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?

Re: How to divide a dataset into columns?

Lacona — Thu, 10 Oct 2019 20:54:12 GMT

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

Thank you so much!

Re: How to divide a dataset into columns?

ballardw — Thu, 10 Oct 2019 21:30:04 GMT

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;

Re: How to divide a dataset into columns?

Ksharp — Fri, 11 Oct 2019 11:52:28 GMT

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;