BookmarkSubscribeRSS Feed
kevsma
Quartz | Level 8

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;
3 REPLIES 3
Tom
Super User Tom
Super User

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.

Reeza
Super User

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.

Ksharp
Super User
/*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;

Ksharp_0-1689680266151.png

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 253 views
  • 3 likes
  • 4 in conversation