SAS Report or VBA Script

[AshokD]

Hi All,

I need a help on creating a SAS report in xls file. On a daily basis , we are generating a report with multiple sheets in a single xls file. 

We need to amend a new sheet with the below data set content which is getting populated daily with different values:-

Obs	text	volume_final
1	Cycle Due = 2	2
2	Cycle Due = 3	2
3	Cycle Due = 4	0
4	Cycle Due = 5	2

* Content of the report should look like as below:-

#1) For example , I am running the job on January month - Day 1 , the above data set values needs to be posted as below and when comes to Day 2 , above data set will populate different values and needs to be append to the below sheet and it continues till day 31 of January.  Also , we need to calculate  the sum up the totals at the end of the report.

* #2)  Above Step #1 , will be followed for all the months in 2019 Jan -Dec , for the upcoming months new column needs to be added when we run the code at the respective months and also for the upcoming years as well.

	2019
	JANUARY	FEBRUARY
	Cycle Due = 2	Cycle Due = 3	Cycle Due = 4	Cycle Due = 5	Cycle Due = 2	Cycle Due = 3	Cycle Due = 4	Cycle Due = 5
D1	2	2	0	2	2	0	0	0
D2	1	0	0	1	3	0	1	1
D3	3	1	0	0	1	1	0	0
D4	1	0	0	0	1	0	0	1
D5	1	0	0	0	2	0	0	0
D6	3	0	0	0	2	0	0	1
D7	2	0	0	0	0	0	0	0
D8	2	0	0	2	4	0	0	0
D9	1	0	1	2	0	0	0	0
D10	1	0	0	0	3	0	0	0
	17	3	1	7	18	1	1	3

Can someone share your thoughts to achieve this report either SAS or VBA script would be fine for this ?

Also , need a suggestion on the base coding part since I don't have a idea on where to start ?

Thanks in advance for your help.

[RW9]

My opinion - as there is always several ways to do any tasks - is to, in order of preference:

1) Use either an application designed for the task.  In this case a web front end reporting suite rather than Excel.  You can design a report which pulls in data from sources and creates nice reports which can be exported if needs be.  This method is most robust, controllable, and flexible but can cost in money and resource to get going.

2) Use the software you "have" to use.  In this case you have to use Excel (presumably), so dump out the SAS data to a CSV, and then add a VBA macro (doesn't even need to be in the destination file as that could be a plain Excel file which opens source and destination), which updates the source destination with the CSV data.  In this method you need to know VBA coding, and a tiny bit of SAS, and can use full functionality of Excel.

3) Rebuild the report either by reading in the source destination into SAS and then updating, writing out, or by somehow appending new data (depends on SAS version, knowledge of SAS).  This would be my least favorite and you can't use all the functionality of Excel.

As such its hard to give any real answer to this question.  I mean your data can be transposed easily enough with a proc transpose:

https://support.sas.com/resources/papers/proceedings09/060-2009.pdf

And you can achieve your output (assuming you are on version 9.3) by using ods excel and proc report, and have a split char in column one as:

define col2 / "2019*JANUARY*Cycle Due = 2";

However if you know no SAS then you will struggle.