I have a report with everything formatted, and I need to update some cell values on a regular basis. Updates were made using PROC SQL, is there a way to let SAS automatically update the cell values after running PROC SQL?
For instance, the cells that need to be updated each time are: A32, A43, A60. Let's say the excel file name is called test.xlsx. Below is a snippet of the PROC SQL code I am running to update those cells:
proc sql;
select count(distinct uci_0) format comma10.0 as caseload,
sum(claim) format dollar12.0 as pos
from pos&fy.
(where=(uci_0 ne . & svscd in ("950", "952") or (include=1 & totind ne . & uci_0 ne .)));
quit;
Don't even try to do that.
Either write the whole report using SAS.
Or export the data that updates to a data sheet (where formatting is of no importance) and use Excel's ability to reference values from other sheets to make the report look like you want.
I concur with Tom and recommend the last option.
Write the output to a new sheet, that gets overwritten as necessary and is linked to the cells that need updating.
/*Here is an example*/
libname x excel 'c:\temp\test.xlsx' scan_text='no';
proc sql;
update x.'Sheet1$'n
set b='z' where a=2;
quit;
libname x clear;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.