topic Re: ODS Excel display data vertically in a report in ODS and Base Reporting

ODS Excel display data vertically in a report

Elliott — Thu, 27 Sep 2018 18:32:25 GMT

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
Month	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.

Re: ODS Excel display data vertically in a report

SuryaKiran — Thu, 27 Sep 2018 20:06:02 GMT

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;

Re: ODS Excel display data vertically in a report

ballardw — Fri, 28 Sep 2018 15:06:34 GMT

@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' .

Re: ODS Excel display data vertically in a report

Elliott — Fri, 28 Sep 2018 14:24:39 GMT

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