BookmarkSubscribeRSS Feed
sascode
Quartz | Level 8

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.

8 REPLIES 8
ballardw
Super User

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
Quartz | Level 8
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
Opal | Level 21

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
Meteorite | Level 14

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
Quartz | Level 8
Thank you.
sascode
Quartz | Level 8
Hello, sounds interesting, I knew i could write R code in SAS environment but not Python.
I appreciate your thoughts.
Ksharp
Super User

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

Suppose you have a excel like:

Ksharp_0-1733450142017.png

 

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:

Ksharp_1-1733450340735.png

 

sascode
Quartz | Level 8
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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 816 views
  • 4 likes
  • 5 in conversation