topic Re: Export Dataset to Replace a Single Sheet in 2016 Excel File in SAS Programming

Export Dataset to Replace a Single Sheet in 2016 Excel File

Nanoputian628 — Mon, 02 Nov 2020 06:03:38 GMT

I know this question has already been asked several times, however unfortunately none of the solutions worked for me.

I have a xlsx file where I have a sheet called "Data" which has raw data (exported from SAS) and then there are other sheets which calculate values based on the "Data" sheet. I need to update the "Data" sheet every month. I would like to be able to export a SAS dataset to this file so that only the "Data" sheet is replaced and all other sheets in the file remain the same.

Previously, I had 64-bit Office 2013 installed onto my computer and I was able to achieve the above by using the below code:

PROC EXPORT DATA= work.dataset
            OUTFILE= "file_path\file_name.xlsx" 
            DBMS = excel REPLACE;
     SHEET = "Data";
RUN;

However, I have now 32-bit Office 2016 installed onto my computer. When I run the above code, I get an error message saying:

ERROR: Connect: Class not registered

When I change the code so that "DBMS = xlsx", the code runs, but then the entire file is now replaced and only the "Data" sheet remains.

I am using SAS 9.3.

Thanks for you help in advance!

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Amir — Mon, 02 Nov 2020 07:36:50 GMT

Hi @Nanoputian628 ,

Have you tried any of the options contained in the following SAS Note?

http://support.sas.com/kb/63/750.html

Kind regards,

Amir.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Nanoputian628 — Mon, 02 Nov 2020 10:09:05 GMT

Hi @Amir,

Thank you for the suggestion. Looking at the link, I believe options 1 and 3 apply to my case. Option 1 I have already tried and as I mentioned, it did not work since the entire file was replaced. I will not be able to do option 3 since I do not have admin access on the PC I am using.

Thanks,

Dilshan

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Ksharp — Mon, 02 Nov 2020 11:46:18 GMT

Did you try XLSX engine ?

libname x xlsx "file_path\file_name.xlsx" ;
proc sql;
drop table x.'Data$'n ; /* x.Data*/
create table x.Data as
select * from work.dataset ;
quit;

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Kurt_Bremser — Mon, 02 Nov 2020 11:50:30 GMT

There have been lots of improvements to the XLSX engine in the meantime. Update your SAS to the current version (9.4 ML7) three days before yesterday.

9.3 is very old now in terms of SAS development.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Amir — Mon, 02 Nov 2020 18:49:13 GMT

Hi @Nanoputian628 ,

Is your administrator able to help?

Kind regards,

Amir.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Reeza — Mon, 02 Nov 2020 20:10:13 GMT

Option 3 is often installed by default these days. Did you try using PCFILES and did it not work? Or did you try seeing if you have it installed but need to start it?

SAS 9.3 was last released in 2012 so it's now about 7/8 years out of date.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

SASKiwi — Mon, 02 Nov 2020 21:09:06 GMT

@Nanoputian628 - Option 2 should also apply as it is a free download from Microsoft. The 64-bit ACE engine components will fix your issue as it will align your SAS bitness with the ACE bitness.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Nanoputian628 — Mon, 02 Nov 2020 21:33:59 GMT

Hi @Amir ,

I could, but it will be a long process and it is not guaranteed they will be able to help. Also, this code should be able to be run by other people in my team, so ideally I would like a solution which can be implemented by others without requiring many changes.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Nanoputian628 — Mon, 02 Nov 2020 21:36:45 GMT

Hi Reeza,

I have tried PCFILES in the past but it did not work. Also, this code should be able to be run by other people in my team (they have the same software as I do), so ideally I would like a solution which can be implemented by others with minimal changes.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Nanoputian628 — Mon, 02 Nov 2020 21:59:43 GMT

Hi @Ksharp,

Thanks for the suggestion. i tried running your code however I got an error stating "The XLSX engine cannot be found". I have a look at accepted answer in the below link:
https://communities.sas.com/t5/SAS-Analytics-U/ERROR-XLSX-engine-cannot-be-found/td-p/567633
It does seem like I do have Access installed on my PC, so not sure what is the problem.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Nanoputian628 — Mon, 02 Nov 2020 22:04:40 GMT

Yes it is annoying to use SAS 9.3, however unfortunately I am unable to install SAS 9.4 onto my PC. Also would like other people in my team (they use SAS 9.3 as well) to be able to run this code as well.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

SASKiwi — Mon, 02 Nov 2020 22:21:26 GMT

@Nanoputian628 - I'm pretty sure the XLSX LIBNAME engine wasn't available in your release. It was only available via PROC IMPORT DBMS = . This SAS Note may be helpful:

https://support.sas.com/kb/51/580.html

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Kurt_Bremser — Tue, 03 Nov 2020 05:36:26 GMT

The current release of SAS software is always free with the license, so there is NO REASON AT ALL to keep on using an old version that does not meet your needs anymore and hampers your work. Get the new version. YOU PAID FOR IT through your license, not using it means throwing money out the window, and any business(wo)man worth a dime sees that as idiocy.

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Ksharp — Tue, 03 Nov 2020 12:08:39 GMT

If you could , open PCFile Server at start menu .
And try pcfile engine .

Re: Export Dataset to Replace a Single Sheet in 2016 Excel File

Reeza — Tue, 03 Nov 2020 17:01:09 GMT

9.3M1 is older (2011) but it indicates you have a license for PC Files there so you should be able to use the PCFILES engine method.