Help using Base SAS procedures

Format transpose

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Format transpose

I have output looks like this:

year        row        meat          col01         col02

2017        1           beef             a1             a2

2017        1           chicken        b1             b2

2017        2           beef             a3             a4

2017        2           chicken        b3             b4

2018        1           beef             a5             a6

2018        1           chicken        b5             b6

2018        2           beef             a7             a8

2018        2           chicken        b7             b8

 

I want to change the format like below:

 

year        row       col        beef         chicken

2017       1           01         a1             b1

2017       1           02         a2             b2

2017       2           01         a3             b3

2017       2           02         a4             b4

2018       1           01         a5             b5

2018       1           02         a6             b6

2018       2           01         a7             b7

2018       2           02         a8             b8

 

Last week I asked a similar question, now I know how to do transpose without column 'year'. But with column 'year, format will look wired. Please help!

 

Thanks!


Accepted Solutions
Solution
2 weeks ago
Super Contributor
Posts: 311

Re: Format transpose

Posted in reply to leonzheng

Adding YEAR to the by statement should work fine for you, no?

 


data have;
input year row meat $col01 $ col02 $;
datalines;
2017        1           beef             a1             a2
2017        1           chicken        b1             b2
2017        2           beef             a3             a4
2017        2           chicken        b3             b4
2018        1           beef             a5             a6
2018        1           chicken        b5             b6
2018        2           beef             a7             a8
2018        2           chicken        b7             b8
;;;;
run;

proc transpose data=have out=want;
  by year row;
  id meat;
  var col01 col02;
run;

View solution in original post


All Replies
Solution
2 weeks ago
Super Contributor
Posts: 311

Re: Format transpose

Posted in reply to leonzheng

Adding YEAR to the by statement should work fine for you, no?

 


data have;
input year row meat $col01 $ col02 $;
datalines;
2017        1           beef             a1             a2
2017        1           chicken        b1             b2
2017        2           beef             a3             a4
2017        2           chicken        b3             b4
2018        1           beef             a5             a6
2018        1           chicken        b5             b6
2018        2           beef             a7             a8
2018        2           chicken        b7             b8
;;;;
run;

proc transpose data=have out=want;
  by year row;
  id meat;
  var col01 col02;
run;
Occasional Contributor
Posts: 15

Re: Format transpose

Posted in reply to snoopy369

It doesn't work,

If I do this column 'row' will be transposed, it will look something like this:

year                      beef         chicken       beef         chicken

2017      row          1              1                 2              2

2017       1             a1            b1               a2            b2

2017       2             a3            b3               a4            b4

2018      row          1              1                 2              2

.

.

.

 

Super Contributor
Posts: 311

Re: Format transpose

Posted in reply to leonzheng
You also need ROW on the by statement. Run the code in my example.
Occasional Contributor
Posts: 15

Re: Format transpose

Posted in reply to snoopy369

you are right, I missed the ROW...

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 233 views
  • 0 likes
  • 2 in conversation