topic Re: Format transpose in SAS Procedures

Format transpose

leonzheng — Wed, 03 Jan 2018 19:15:59 GMT

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!

Re: Format transpose

snoopy369 — Wed, 03 Jan 2018 19:18:34 GMT

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;

Re: Format transpose

leonzheng — Wed, 03 Jan 2018 19:32:08 GMT

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

Re: Format transpose

snoopy369 — Wed, 03 Jan 2018 19:32:46 GMT

You also need ROW on the by statement. Run the code in my example.

Re: Format transpose

leonzheng — Wed, 03 Jan 2018 19:35:11 GMT

you are right, I missed the ROW...