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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.