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??
@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;
@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;
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?
Forgot to mention, would there be a way to provide a bottom line grand total and a grand total by rows
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: