Export Dataset to Replace a Single Sheet in 2016 Excel File

[Nanoputian628]

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!

[Amir]

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.

[Nanoputian628]

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

[Amir]

Hi @Nanoputian628 ,

Is your administrator able to help?

Kind regards,

Amir.

[Nanoputian628]

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.  

[Reeza]

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.

[Nanoputian628]

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.   

[SASKiwi]

@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.

[Ksharp]

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;

[Nanoputian628]

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.

[SASKiwi]

@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

[Ksharp]

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

[Reeza]

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.

[Kurt_Bremser]

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.

[Nanoputian628]

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.