- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
/*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;