Need help with tabular report

[vicky07]

Hello,

I am new to tabulate reporting need help with a report that I am trying to build. Below is my sample data.

data test;
input date : mmddyy10. product $ value ;
format date mmddyy10.;
cards;
3/17/2019 CLE 3785
3/18/2019 CLE 2245
3/19/2019 CLE 1125
3/20/2019 CLE 24589
3/21/2019 CLE 0
3/18/2019 DAG 5200
3/24/2019 DAG 4700
3/25/2019 DAG 3345
3/26/2019 DAG 3800
3/17/2019 DAG 111
3/18/2019 AGR 333
3/19/2019 AGR 2
3/20/2019 AGR 15
3/21/2019 AGR 0
3/24/2019 KML 1245
3/25/2019 KML 7789
3/26/2019 KML 6189
3/27/2019 KML 6030
3/28/2019 AGR 62
;
run;

I need my output in  tabular form as below. The numbers should be centered and comma formatted. 

Date	Product Volume
	CLE	DAG	AGR	KML
3/17/2019	3,785	111	-	-
3/18/2019	2,245	5,200	333	-
3/19/2019	1,125	-	2	-
3/20/2019	24,589	-	15	-
3/21/2019	-	-	-	-
3/24/2019	-	4,700	-	-
3/25/2019	-	3,345	-	-
3/26/2019	-	3,800	-	-
3/17/2019	-	-	-	-
3/18/2019	-	-	-	-
3/19/2019	-	-	-	-
3/20/2019	-	-	-	-
3/21/2019	-	-	-	-
3/24/2019	-	-	-	1,245
3/25/2019	-	-	-	7,789
3/26/2019	-	-	-	6,189
3/27/2019	-	-	-	6,030
3/28/2019	-	-	62	-
Total	31,744	17,156	412	21,253

Thank you for your help!

[ed_sas_member]

Hi @vicky07 

Something like this ?

proc report data=test completerows ;

	column date _date product, value;
	
	define date / group noprint;
	define _date / computed 'Date';
	define product / across 'Product Volume' order=data;
	define value / analysis f=comma8.0 center;
	
	rbreak after / summarize ;
	
	compute _date / char;
		if _break_='_RBREAK_' then _date = "Total";
		else _date = put(date, mmddyy10.);
	endcomp;
run;

[vicky07]

Hi ed_sas_member,

Firstoff , thanks for your response. Your solution is close enough. Couple things:

1) Not sure why the "DAG" product is not showing for 3/18/2019 date in the report. It is missing the 5,200 value.

2) I don't want to display the word "value" under product names in the report

3) I know i didn't mention this in my initial email but I want the report/output to be exported to excel with table borders. 

is it possible to correct the above changes?

Thanks. 

[ed_sas_member · Accepted solution]

Hi @vicky07 

Thank you for your feedback.

I have tried to modify the code according to your request. I think we are getting closer to the result 🙂

ods excel file="path/file.xlsx";

proc report data=test completerows headline headskip
			style(column)={rules=ALL frame=BOX};

	column date _date product, value;
	
	define date / group noprint;
	define _date / computed 'Date';
	define product / across 'Product Volume' order=data;
	define value / analysis f=comma8.0 center '';
	
	rbreak after / summarize ;
	
	compute _date / length=10;
		if _break_='_RBREAK_' then _date = "Total";
		else _date = put(date, mmddyy10.);
	endcomp;
run;

ods excel close;

[vicky07]

I am getting the below error message:

ods excel file="path\file.xlsx" ;
ERROR: Unable to load module 'SpreadsheetML' from template store!
ERROR: No body file. EXCEL output will not be created.

What am i doing wrong?

Thanks.

[ed_sas_member]

Hi Vicky,

Have you updated "Path/file.xls with your file name and exact path ?

e.g; "C:/myfile.xlsx"

Regarding the ERROR message, what SAS version are you using? ODS Excel was introduced in 9.4 TS1M3 or something like that.

I suggest that you try ods tagsets instead:

ods tagsets.excelxp file = ‘path/file.xls’;

/*code with proc report*/

ods tagsets.excelxp close;

Best,

[vicky07]

I had 9.3 version but ods tagsets worked. Thank you very much!!!

[ed_sas_member]

Wonderful!
Thank you @vicky07