topic Re: How to export an excel file to a specific workbook on my i drive? in SAS Programming

How to export an excel file to a specific workbook on my i drive?

LMSSAS — Wed, 08 Jun 2022 17:40:28 GMT

Hello, I have a project that I run in SAS and then export the tables into differernt excel sheets. I first do a proc export to my folder in SAS, then do another proc export to the lcoal i drive. I have a excel workbook that is stored on my i that I use to create visuals. How can I get the proc export that I drop to the i drive to land in the workbook with my visuals so I odn't have to copy and paste data into excel. I hope this makes sense if I need to clarify more please let me know. Below is my proc export code Thank you. currently the files do not drop into my to excel workbook and I do not know why

proc export data=Apps_Per_Agent outfile="/u/&sysuserid./Apps_Per_Agent.xlsx"
replace dbms=xlsx;
sheet="Apps_Per_Agent";

proc export data=Apps_Per_Agent 
outfile= "I:\Health Business Operations\Channel Service Organization\DARU\01 - Reporting Tools\Medicare Sales Agencies\VBA Code for email\Scorecard VBA for Email and PDF\Medicare Scorecard TOH.xlsm"
dbms=xlsx
replace; 
sheet="Apps_Per_Agent";
run;

Re: How to export an excel file to a specific workbook on my i drive?

ballardw — Wed, 08 Jun 2022 18:07:29 GMT

Personally, I much prefer "visuals" from SAS code directly. The use ODS EXCEL or similar to put the graphs and data into a report file.

Export, as I expect you have found, will replace files.

You can try using the LIBNAME xlsx to write data into an existing file to replace an existing sheet. You may still need to refresh links between your "visuals" and the data.

I hope you didn't place the visual and the data on the same sheet. That would likely make this extremely difficult.

Re: How to export an excel file to a specific workbook on my i drive?

Tom — Wed, 08 Jun 2022 18:08:19 GMT

If you are expecting SAS to paste something into the middle of an existing worksheet then that won't work.

Instead of SAS write to a new worksheet.

Then modify your Excel workbook to use whatever data it finds there (or copy it where it needs it).

Re: How to export an excel file to a specific workbook on my i drive?

LMSSAS — Wed, 08 Jun 2022 18:14:30 GMT

No, I didn't place the visuals and data on the same sheet. I will try using the Libname, I just want to replce the existing sheets in the workbook each week when I run the program so I don't have to copy and paste the new data evey week. I can go in and refresh the data in the workbook. Thank you!!

Re: How to export an excel file to a specific workbook on my i drive?

LMSSAS — Wed, 08 Jun 2022 18:15:05 GMT

ok, thank you!