ODS Excel display data vertically in a report

[Elliott]

Hello,

I need some ODS help.  I am not sure this is even possible but this is what I have and what I need the ODS to display:

I have a data table of historical data that looks like this:

Month	Title	Errors	Volume	date
2018MAY	Region1	5	1,520	01MAY2018
2018MAY	Region2	1,200	12,587,659	01MAY2018
2018MAY	Region3	16	5,862	01MAY2018
2018MAY	Region4	182	578	01MAY2018
2018MAY	RegionTotal	0	19,468	01MAY2018
2018JUN	Region1	57	1,875	01JUN2018
2018JUN	Region2	100	19,587,659	01JUN2018
2018JUN	Region3	16	3,762	01JUN2018
2018JUN	Region4	0	777	01JUN2018
2018JUN	RegionTotal	10	18,432	01JUN2018

I need the report that ODS creates to look like this:

Mock up:

Month		Region_Total			Region1			Region2			Region2			Region2
		Volume	Errors		Volume	Errors		Volume	Errors		Volume	Errors		Volume	Errors
2018 - May		12,595,619	1403		1,520	5		12,587,659	1,200		5,862	16		578	182
2018 - June		19,594,073	173		1,875	57		19,587,659	100		3,762	16		777	0
2018 - July		…	…		…	…		…	…		…	…		…
…

 Is it possible to get the data to display like this?  I am using SAS 9.4 on Linux, will run from cmd line when in production but development is being done in SAS Enterprise Guide 7.15.  The output will be a .xlsx workbook.  The user actually wants a rolling 13 months of data so this is only a representation of the data the report will show.

Thank for any assistance.  

[SuryaKiran]

Try proc report

data have;
infile datalines dlm='|' dsd missover;
input Month:$10. Title:$15. Errors :$15. Volume:$15. date :$15.;
datalines;
2018MAY|Region1|5|1,520|01MAY2018
2018MAY|Region2|1,200|12,587,659|01MAY2018
2018MAY|Region3|16|5,862|01MAY2018
2018MAY|Region4|182|578|01MAY2018
2018MAY|RegionTotal|0|19,468|01MAY2018
2018JUN|Region1|57|1,875|01JUN2018
2018JUN|Region2|100|19,587,659|01JUN2018
2018JUN|Region3|16|3,762|01JUN2018
2018JUN|Region4|0|777|01JUN2018
2018JUN|RegionTotal|10|18,432|01JUN2018
;
run;


proc report data=have;
column month title,(errors volume) ;
define month/Group;
define title/across;
define errors/Group;
define volume/Group;
run;

[ballardw · Accepted solution]

---

@Elliott wrote:

Hello,

I need some ODS help.  I am not sure this is even possible but this is what I have and what I need the ODS to display:

I have a data table of historical data that looks like this:

Month	Title	Errors	Volume	date
2018MAY	Region1	5	1,520	01MAY2018
2018MAY	Region2	1,200	12,587,659	01MAY2018
2018MAY	Region3	16	5,862	01MAY2018
2018MAY	Region4	182	578	01MAY2018
2018MAY	RegionTotal	0	19,468	01MAY2018
2018JUN	Region1	57	1,875	01JUN2018
2018JUN	Region2	100	19,587,659	01JUN2018
2018JUN	Region3	16	3,762	01JUN2018
2018JUN	Region4	0	777	01JUN2018
2018JUN	RegionTotal	10	18,432	01JUN2018

I need the report that ODS creates to look like this:

Mock up:

Month		Region_Total			Region1			Region2			Region2			Region2
		Volume	Errors		Volume	Errors		Volume	Errors		Volume	Errors		Volume	Errors
2018 - May		12,595,619	1403		1,520	5		12,587,659	1,200		5,862	16		578	182
2018 - June		19,594,073	173		1,875	57		19,587,659	100		3,762	16		777	0
2018 - July		…	…		…	…		…	…		…	…		…
…

 Is it possible to get the data to display like this?  I am using SAS 9.4 on Linux, will run from cmd line when in production but development is being done in SAS Enterprise Guide 7.15.  The output will be a .xlsx workbook.  The user actually wants a rolling 13 months of data so this is only a representation of the data the report will show.

Thank for any assistance.  

---

And we now have a classic reason why we frequently ask for data in the form of a data step. We don't know what any of your variable types are and they can seriously effect what is possible with the data as is.

You are going to have some fun with having a partial total that you do not want included in a final total (guessing from the numbers shown)

This is a different though somewhat similar data set to that provided by @SuryaKiran because I'm using a different approach.

data have;
infile datalines dlm='|' dsd missover;
input Month:$10. Title:$15. Errors :comma6. Volume:comma12. date :date9.;
datalines;
2018MAY|Region1|5|1,520|01MAY2018
2018MAY|Region2|1,200|12,587,659|01MAY2018
2018MAY|Region3|16|5,862|01MAY2018
2018MAY|Region4|182|578|01MAY2018
2018MAY|RegionTotal|0|19,468|01MAY2018
2018JUN|Region1|57|1,875|01JUN2018
2018JUN|Region2|100|19,587,659|01JUN2018
2018JUN|Region3|16|3,762|01JUN2018
2018JUN|Region4|0|777|01JUN2018
2018JUN|RegionTotal|10|18,432|01JUN2018
;
run;

proc tabulate data=have;
   where title ne 'RegionTotal';
   class date title;
   format date yymon.;
   var errors volume;
   table date='',
         (all="Region Total" title)*(errors volume)*sum=''*f=comma12.
         /box='Month'
   ;
run;

If your Month is a date variable with appropriate format you can use that in place of DATE where I did. Purpose: to sort properly otherwise 2018JUN comes before 2018MAY when character variables. If your DATE variable is not an actual DATE variable with date9. format then you really need to ensure one of the two is actually a DATE value so they will sort and group properly.

You did not state that REGIONTOTAL title values were to be excluded. I had to trace that requirement as that was the only way I could get the total to come close to matching yours. If errors might be missing for any of the values you can make the output show a 0 by adding: misstext='0' after the BOX='Month' .

[Elliott]

This is wonderful, one more question.. how do I get the title variable name to not display at the top?

I tried a label stmt with title = '' but then instead of the word title it had ' 

Thanks