topic Re: Proc Transpose and Eventual Proc Report Question in SAS Programming

Proc Transpose and Eventual Proc Report Question

Q1983 — Thu, 09 May 2019 20:45:26 GMT

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??

Re: Proc Transpose and Eventual Proc Report Question

ballardw — Thu, 09 May 2019 21:12:46 GMT

@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;

Re: Proc Transpose and Eventual Proc Report Question

Q1983 — Fri, 10 May 2019 01:36:00 GMT

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?

Re: Proc Transpose and Eventual Proc Report Question

Q1983 — Fri, 10 May 2019 02:03:58 GMT

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

Re: Proc Transpose and Eventual Proc Report Question

ballardw — Fri, 10 May 2019 15:03:22 GMT

@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;