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
@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.
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 ?
@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.
Thanks, I try to use ODS
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.
I have considered this solution, but with the new version of excel installed, the Add-In are not available.
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.
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://support.sas.com/resources/papers/proceedings14/1793-2014.pdf
Kind regards
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
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: