Writing to Specific Cells in an Existing Excel File

ito-maori

In a local environment (on the same machine), SAS can use DDE (Dynamic Data Exchange) to write data to specific cells in an existing Excel file. However, this is not possible when the SAS session is run on a remote machine. DDE alternatives, such as ODS (Output Delivery System for Excel) and SAS/ACCESS to PC Files, do not allow data to be written to specific cells in existing Excel files.

We would like to see the functionality of ODS (Output Delivery System for Excel) and SAS/ACCESS to PC Files improved so that data can be output to specific cells in a sheet of an Excel file that already contains data. We have received strong requests for this function not only from our company but also from our contractors who use the environment provided by our company to perform analysis with SAS.

For example, in Python, the library openpyxl can be used to write data directly into specific cells in an existing Excel file without the need for an Excel application. If this is possible in other languages, it may also be possible to implement similar functionality in SAS through alternative means. If implemented, it would enhance the value of SAS as software.

Ksharp

https://communities.sas.com/t5/SAS-Programming/Manipulating-excel-workbook/m-p/952647

ito-maori

@KsharpThank you for your prompt reply. I confirmed your comments . Thank you for presenting a very useful method. I think the method you showed us was to change the value in column B (weight) for those with a specific value in column A (age), but is it possible to state, for example, to change the value in row 2 column 3 to 12?

Ksharp

Sorry. I can't . The number of columns has been fixed once submitting the LIBNAME statement, so you can't change value in a absent column.

ito-maori

@Ksharp Thank you for your reply. It is quite difficult. Since visitors to our company have limited time to access the data we manage, we expect to use an Excel file like a template in advance and put the result values of the analysis into the existing Excel file. We hope you have some useful ideas, and if you have any wisdom to share, we would appreciate it if you could share it with us.

Ksharp

The key point here is the number of columns in excel file.

Once the first row have been populated the variable name in your excel template file, that would be easy .

For example:

100 is the max number of columns you could used and in the first row you have populated all these variable name, and you can manipulate the exce cell as I did .

ito-maori

@Ksharp　Thank you for your useful comments. Unfortunately, sometimes the templates that companies prepare are not as simple as a specific column being assigned to a specific variable, but rather a complex structure that is intended to be a report. I wonder if it would be possible to devise a way to successfully put values into a file with the following image (which is also quite simplified)

fruits	yen	vitamin C		
banana	300			
apple	400			
				
vegetable	yen	vitamin A	vitamin C	cost
tomato				
cabage				
onion

Ksharp

If you do not need to populate value at right side of "cost", that would be OK.

Otherwise, you might buy a ticket to http:\\support.sas.com

ito-maori

@Ksharp
Thank you for your reply. I understood your reply to be that perhaps the method of assigning each variable, even this one, would be useful. However, what we are looking for is a way to specify a specific cell and put a value in it, which we believe cannot be met in the case of variables. In fact, we have already contacted SAS support (Japan) and they were unable to respond and directed us to this site. We would be grateful and feel that the person in charge at SAS headquarters would see this description and consider responding.

Ksharp

Yes. You are on right way. You can contacted SAS support (US) by http:\\support.sas.com