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