topic Re: Update Excel Sheet in SAS Programming

Update Excel Sheet

EnricoCola — Tue, 16 Mar 2021 16:57:24 GMT

Hi, I'm trying to update an excel file. This file is a pre-formatted template that I use as stencil to generate multiple files. It contains several sheets, a sheet in which there is a sasout and others that have formulas with links to the first. These sheets are small reports and have a header and a table with few rows that should be replicated depending on the number of rows in the db sas.
The steps to take are as follows:
1- Update the sasout sheet
2- Copy the rows present in the report sheet
3- Select n rows according to the number of rows of the SAS db
4- Insert the copied lines

Are there alternative methods to DDE?
Thank you

Re: Update Excel Sheet

Reeza — Tue, 16 Mar 2021 18:06:07 GMT

1. Replace entire report with a formatted report from ODS EXCEL
2. Update your Excel template to use Tables and update your SASOUT table only and change it to allow the rest to flow through so step 2-4 should disappear. You can use FCOPY to copy the template to a new file.
3. Investigate requirements again and see if another tool is more appropriate, such as a BI tool like Visual Analytics (Tableau/PowerBI).

Re: Update Excel Sheet

SASKiwi — Tue, 16 Mar 2021 19:09:44 GMT

Another way to easily update an Excel Sheet is to use the SAS Add-in to MS Office. You are required to run the update tasks manually though from the SAS Menu in Excel.

Re: Update Excel Sheet

EnricoCola — Wed, 17 Mar 2021 08:07:55 GMT

1. Replace entire report with a formatted report from ODS EXCEL

Re : I won't rewriting all formulas, even complex . In this way my colleagues can modify the formulas without knowing sas

2. Update your Excel template to use Tables and update your SASOUT table only and change it to allow the rest to flow through so step 2-4 should disappear. You can use FCOPY to copy the template to a new file.

RE: The problem is that the length of the sasout is not standard

3. Investigate requirements again and see if another tool is more appropriate, such as a BI tool like Visual Analytics (Tableau/PowerBI).

RE: unavaible

I find a code that use VBA for modify excel:

options noxwait noxsync;
%let vba_loc=%sysfunc(getoption(WORK))\vbscript.vbs;
%let open_workbook = &path.\Analisi\Test.xlsx;
data _null_;
file "&vba_loc";
put "Set objExcel = CreateObject(""Excel.Application"") ";
put "objExcel.Visible = True ";
put "objExcel.DisplayAlerts=False";
put "Set wb = objExcel.Workbooks.Open(""&open_workbook"")";
put "wb.sheets(""headings"").Range(""B12"") = ""Prova""";
put "wb.save";
x "'&vba_loc\'";
run;

But I haven't found everything I need. It can be a solution ?

Re: Update Excel Sheet

EnricoCola — Wed, 17 Mar 2021 08:11:25 GMT

I have considered this solution, but with the new version of excel installed, the Add-In are not available.

Re: Update Excel Sheet

Kurt_Bremser — Wed, 17 Mar 2021 08:17:32 GMT

Doing BI work in Excel when SAS is at hand is at best stupid. Excel provides so many opportunities for undetected mistakes that I (and I am not alone in this) consider using it for business critical tasks as gross negligence.

In our company, internal audit works hard at preventing such practice.

I second what others have suggested: move the whole calculation into SAS, and use Excel for presentation only.

Re: Update Excel Sheet

ammarhm — Wed, 17 Mar 2021 08:49:22 GMT

I second what everyone else has said about moving the whole solution to SAs instead.

However, if that is not possible for some reason, you might want to look at the following two documents they might set you in the right direction.

https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf

https://support.sas.com/resources/papers/proceedings14/1793-2014.pdf

Kind regards

Re: Update Excel Sheet

EnricoCola — Wed, 17 Mar 2021 08:52:12 GMT

Thanks for the advice, unfortunately I face different opinions and habits that I am not able to eradicate. So I must find other solutions that satisfy my customers

Re: Update Excel Sheet

Reeza — Wed, 17 Mar 2021 15:57:32 GMT

@EnricoCola wrote:

2. Update your Excel template to use Tables and update your SASOUT table only and change it to allow the rest to flow through so step 2-4 should disappear. You can use FCOPY to copy the template to a new file.

RE: The problem is that the length of the sasout is not standard

The new Tables feature in Excel makes that a moot point, the table grows as the data does.

@EnricoCola wrote:

1. Replace entire report with a formatted report from ODS EXCEL

Re : I won't rewriting all formulas, even complex . In this way my colleagues can modify the formulas without knowing sas

ODS Excel allows you to write formulas to cells, not just raw data, so your colleagues maintain their ability to update the file.

Re: Update Excel Sheet

EnricoCola — Wed, 17 Mar 2021 16:05:34 GMT

Thanks, I try to use ODS