Manipulating excel workbook

[sascode]

Hello,

I do have a question as follows:
I have an excel workbook which has multiple sheets and it generates some graphs with dynamic titles and footnotes using VBA language based on data in all these multiple sheets. Quarterly, only thing i need to do is to update data ( copy from SPSS and paste in excel)  in each sheet and all graphs are updated accordingly. Recently, using R(openxlsx2 package), I am able to load the entire workbook in R environment , update it(only those specific cells that need to be updated)  and export back without damaging its existing format and all VBA coding behind.

Now I am wondering can this be achieved in SAS if am asked to shift from R to SAS programming?

Thank you.

[ballardw]

The answer without an actual concrete is example is likely: Maybe.

You say "update specific cells" but without any example of what the sheets look like it may or may not be "easy" to do such with SAS. The answer is more likely to be "no" if the data is commingled with output (graphs or pivot tables) and if columns in the sheets have mixed data types, i.e. row 2 is numeric, row 3 is text, row 4 is a date .

Personally, if it were my project the only place Excel would come in would be as a container for SAS generated output. VBA code can be replaced by SAS. Graphs can be created by SAS. Creating text to display on graphs from data is not a difficult task.

One of my introductions to Excel after having used SAS for nearly 10 years was to create bunch of related graphs from data. I found the whole process so cumbersome as with SAS I would have debugged one graph and then used a BY statement to make the different graphs. Putting stuff on different "sheets" and manually linking the data to each graph... gaah!

[sascode]

Hello,
I agree with your idea and I am hoping I will have the permission to transfer the entire project to SAS.
Thank you vey much for your reply.

[Patrick]

Using SAS you can replace a sheet in an existing Excel workbook using Proc Export. Except for using DDE (which is obsolete since many years) you can't change cell values in an existing sheet.

For changing selected cell values I'd be using Python eventually calling the script out of SAS. The reason for Python is it's degree of integration into SAS. The SAS Viya Studio version even comes with a built-in Python editor.

[Sajid01]

Hello @sascode 
To your question "Now I am wondering can this be achieved in SAS if am asked to shift from R to SAS programming?" the answer is simply speaking a big yes. 

[sascode]

Thank you.

[sascode]

Hello, sounds interesting, I knew i could write R code in SAS environment but not Python.
I appreciate your thoughts.

[Ksharp]

SAS has some limited to update a cell of a excel file, but here is an example:

Suppose you have a excel like:

After running the following code :

libname x excel 'c:\temp\temp.xlsx' scan_text=no;
proc sql;
update x.'Sheet1$'n 
set weight=1
 where age=14;
quit;libname x clear;

You could get:

[sascode]

Hello, although i do not pretend to be proficient in SAS, I had the idea it has some limitations . I like your example, sounds interesting.
Thank you.