topic Re: SAS code to update specific cell values in Excel in SAS Programming

SAS code to update specific cell values in Excel

kevsma — Mon, 17 Jul 2023 17:31:07 GMT

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;

Re: SAS code to update specific cell values in Excel

Tom — Mon, 17 Jul 2023 19:01:37 GMT

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.

Re: SAS code to update specific cell values in Excel

Reeza — Mon, 17 Jul 2023 19:21:43 GMT

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.

Re: SAS code to update specific cell values in Excel

Ksharp — Tue, 18 Jul 2023 11:37:29 GMT

/*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;