Proc Transpose and Eventual Proc Report Question

[Q1983]

data test;

length YEAR $4.;

infile datalines;

input YEAR $ CHAPTER $ MONTH $ CNT;

return;

datalines;

2018 CN MAY 6

2018 CN JUN 8

2018 CN JUL 11

2015 NT NOV 2

2016 ST JAN 4

2016 ST FEB 4

2015 PAC APR 4

;

run;

PROC SORT = TEST;BY YEAR;RUN;

PROC TRANSPOSE = TEST out = TEST1 (drop = _NAME_ _LABEL_) ;

BY YEAR CHAPTER;

ID MONTH;

VAR CNT;

run;

PROC TRANSPOSE PRODUCES THE FIRST DATASET

YEAR	CHAPTER	NOV	APR	JAN	FEB	MAY	JUN	JUL
2015	NT	2
2015	PAC		4
2016	ST			4	4
2018	CN					6	8	11
Desire for the data to look like this
	2015		2016
CHAPTER	NOV	APR	JAN	FEB	MAY	JUN	JUL
NT	2
PAC		4
ST			4	4
CN					6	8	11

I WOULD THEN USE THIS IN A PROC REPORT TO GRAB ALL TOTALS. SO MY QUESTION IS WOULD I NEED TO GET THE DATASET TO DISPLAY AS DESIRED IN THE PROC REPORT OR CAN IT BE DONE IN THE TRANSPOSE??

[ballardw]

---

@Q1983 wrote:

data test;

length YEAR $4.;

infile datalines;

input YEAR $ CHAPTER $ MONTH $ CNT;

return;

datalines;

2018 CN MAY 6

2018 CN JUN 8

2018 CN JUL 11

2015 NT NOV 2

2016 ST JAN 4

2016 ST FEB 4

2015 PAC APR 4

;

run;

PROC SORT = TEST;BY YEAR;RUN;

PROC TRANSPOSE = TEST out = TEST1 (drop = _NAME_ _LABEL_) ;

BY YEAR CHAPTER;

ID MONTH;

VAR CNT;

run;

PROC TRANSPOSE PRODUCES THE FIRST DATASET

YEAR	CHAPTER	NOV	APR	JAN	FEB	MAY	JUN	JUL
2015	NT	2
2015	PAC		4
2016	ST			4	4
2018	CN					6	8	11
Desire for the data to look like this
	2015		2016
CHAPTER	NOV	APR	JAN	FEB	MAY	JUN	JUL
NT	2
PAC		4
ST			4	4
CN					6	8	11

I WOULD THEN USE THIS IN A PROC REPORT TO GRAB ALL TOTALS. SO MY QUESTION IS WOULD I NEED TO GET THE DATASET TO DISPLAY AS DESIRED IN THE PROC REPORT OR CAN IT BE DONE IN THE TRANSPOSE??

---

What happened to the data from the year 2018?

Your proc sort is syntactically incorrect;

Any particular reason that NOV comes before APR for a year?

You may not need proc transpose at all depending on how married you  are to weird data orders

And please post code in a code box. The windows insert html and sometimes weird stuff happens plus pasting code into the SAS editor has a bunch of extra lines.

PROC SORT data = TEST;
BY YEAR;
RUN;

proc tabulate data=test;
   class chapter /order=data;
   class year  month;
   var cnt;
   tables chapter='',
          year=''*month=''*cnt=''*sum=''
          /misstext=' ' box=chapter
   ;
run;

[Q1983]

The proc tabulate actually resolved the issue.  Just one more question.  The months need to display in actual month order, JAN,FEB,MAR etc.  Is there a way to apply in month order since I used the

monname3. function?

[Q1983]

Forgot to mention, would there be a way to provide a bottom line grand total and a grand total by rows

[ballardw · Accepted solution]

---

@Q1983 wrote:

Forgot to mention, would there be a way to provide a bottom line grand total and a grand total by rows

---

Order issues with dates are usually best solved by using actual SAS date variables.

The Proc Tabulate key word 'All' can be used to create summaries by groups.

data test;
   length YEAR $4.;
   infile datalines;
   input YEAR $ CHAPTER $ MONTH $ CNT;
   actualdate=input(catt('01',month,year),date9.);
   format actualdate date9.;
datalines;
2018 CN MAY 6
2018 CN JUN 8
2018 CN JUL 11
2015 NT NOV 2
2016 ST JAN 4
2016 ST FEB 4
2015 PAC APR 4
;
run; 

PROC SORT data = TEST;
BY YEAR;
RUN;

proc tabulate data=test;
   class chapter /order=data;
   class year  actualdate;
   format actualdate monname3.;
   var cnt;
   tables chapter='' all=' Column Total',
          year=''*actualdate=''*cnt=''*sum=''  all='Row Total'*cnt=''*sum
          /misstext=' ' box=chapter
   ;
run;

If you wanted to do a row total within each year:

proc tabulate data=test;
   class chapter /order=data;
   class year  actualdate;
   format actualdate monname3.;
   var cnt;
   tables chapter='' all=' Column Total',
          year=''*(actualdate='' All='Year total')*cnt=''*sum=''  all='Row Total'*cnt=''*sum
          /misstext=' ' box=chapter
   ;
run;