Is there anyway to import a file xlsm to xlsx in SAS ?

[Phil_NZ]

Hi experts!

I had a look from Google, some forums, but it seems that whether we can import xlsm to xlsx types are still under controversial.

I am wondering if there is any solution to transform xlsm file to xlsx file in SAS so far? I mean, I can do it manually by saving function from Excel. However, I have around 65 files and it is not optimal to do it manually. Even in that case, I also can search some Escel VBA codes to change the type of files from xlsm to xlsx in one folder. However, I want to do something consistent (like know more about SAS and see how far SAS can go).

I wish I can receive your idea about that.

Warmest regards!

[SASKiwi]

SAS isn't a tool for converting Excel file formats. You can read Excel worksheets into SAS datasets and you can write SAS datasets to Excel worksheets but there is no guarantee that if you read from one Excel workbook and write out to another that the "copy" will be the same as the original.

[Tom]

XLSM is just XLSX with some extra microsoft objects for storing macros.  You should be able to read the data in a XLSM file using the XLSX engine.  SAS cannot do anything with any macros you might have in there.

libname x xlsx 'c:\downloads\test1.xlsm' ;
proc copy inlib=x outlib=work; run;

[Phil_NZ]

Hi @Tom and @Shmuel , thank you for your notices, could you please tell me why this code does not work while I am trying to convert from xlsm to xlsx? Many thanks

options compress=yes reuse=yes;

options mergenoby=error;


%macro ImportAndTranspose(
      File=
      , StartSheet=
      , EndSheet=
   );

	libname myxl xlsx &File.;
    /*line changed*/
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;

    %if &i=1 %then %do;

     data sheet&i.;
	  set myxl.sheet&i.;
      range= "Sheet1$A:X";
      getnames= yes;
	 run;

     proc sort data= sheet&i.;
      by Type;
     run;
	%end;
	/* A list of code afterwards to do while i <> 1*/

   %end;
   
%mend;

%ImportAndTranspose(
      File= C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm
      , StartSheet= 1
      , EndSheet= 5);

The error log is

64          C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm
            _
            22
ERROR: Libref MYXL is not assigned.
ERROR: Error in the LIBNAME statement.
ERROR: Libref MYXL is not assigned.

ERROR 22-7: Invalid option name C.

[Shmuel]

Having line 

libname myxl xlsm &File.;

enables read a sheet by set myxl.<sheet name>;

You cannot use this method when the engine is changed into xlsx.

I suggest to open the xlsx excel file and keep it as  a xlsm file.

[Phil_NZ]

Hi @Shmuel , thank you for your suggestion, but when I run it again, the problem still be there, I am wondering if there is anyway to sort it out.

options compress=yes reuse=yes;

options mergenoby=error;
/* If a MERGE statement used w/o a BY statement, the LOG presents error message*/
%macro ImportAndTranspose(
      File=
      , StartSheet=
      , EndSheet=
   );

	libname myxl xlsm &File.;/*line changed*/
    
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;

    %if &i=1 %then %do;
	  set myxl.sheet&i.;/*line changed*/
      range= "Sheet1$A:X";
      getnames= yes;
	 run;

     proc sort data= sheet&i.;
      by Type;
     run;
	%end;
	/* A list of code afterwards to do while i <> 1*/

   %end;
   
%mend;

%ImportAndTranspose(
      File= C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm
      , StartSheet= 1
      , EndSheet= 5);

The file is currently under xlsm format, and I also delete the line 

data sheet&i.;

[Tom]

---

@Shmuel wrote:

Having line 

libname myxl xlsm &File.;

enables read a sheet by set myxl.<sheet name>;

You cannot use this method when the engine is changed into xlsx.

I suggest to open the xlsx excel file and keep it as  a xlsm file.

---

That's exactly backwards. The engine is XLSX.

125   libname x xlsx 'c:\downloads\test1.xlsm' ;
NOTE: Libref X was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: c:\downloads\test1.xlsm
126   proc copy inlib=x outlib=work; run;

NOTE: Copying X.SHEET1 to WORK.SHEET1 (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 1 observations and 2 variables.
NOTE: There were 1 observations read from the data set X.SHEET1.
NOTE: The data set WORK.SHEET1 has 1 observations and 2 variables.
NOTE: Copying X.SHEET2 to WORK.SHEET2 (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 1 observations and 2 variables.
NOTE: There were 1 observations read from the data set X.SHEET2.
NOTE: The data set WORK.SHEET2 has 1 observations and 2 variables.
NOTE: Copying X.SHEET3 to WORK.SHEET3 (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 0 observations and 0 variables.
NOTE: There were 0 observations read from the data set X.SHEET3.
NOTE: The data set WORK.SHEET3 has 0 observations and 0 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.15 seconds
      cpu time            0.06 seconds


127   libname x xlsm 'c:\downloads\test1.xlsm' ;
ERROR: The XLSM engine cannot be found.
ERROR: Error in the LIBNAME statement.
128   proc copy inlib=x outlib=work; run;

ERROR: Libref X is not assigned.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.

Note the real problem with the macro code was that the LIBNAME statement did not have quotes around the filename. Either add the quotes in the call to the macro or change the macro code to add the quotes.

[Shmuel]

@Tom , @Phil_NZ  - sorry for my confusion. I have checked it:

     Engine XLSX enables read both XLSX and XLSM excel formats.

     Engine XLSM is erroneous.

[ballardw]

This line in the body of your macro

libname myxl xlsx &File.;

Resolves to

libname myxl xlsx C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm;

With the value of your macro variable &file.

The file name has to be quotes. So maybe

libname myxl xlsx "&File.";

would fix the Libname not assigned error.

Remember, you can set OPTIONS MPRINT; before running the macro to see the code as generated, which usually means the error messages make more sense.

[Phil_NZ]

Hi @ballardw !

Thank you very much for your help, but I also change a little bit to suit your suggestion but some errors still occur.

The code is as below:

options compress=yes reuse=yes;

options mergenoby=error;

OPTIONS MPRINT;

%macro ImportAndTranspose(
      File=
      , StartSheet=
      , EndSheet=
   );

	libname myxl xlsx "&File.";/*line changed*/
    
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;

    %if &i=1 %then %do;

	  set myxl.sheet&i.;/*line changed*/
      range= "Sheet1$A:X";
      getnames= yes;
	 run;

     proc sort data= sheet&i.;
      by Type;
     run;
	%end;
	/* A list of code afterwards to do while i <> 1*/

   %end;
   
%mend;

%ImportAndTranspose(
      File= C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm
      , StartSheet= 1
      , EndSheet= 5);

The log file is as below:

MPRINT(IMPORTANDTRANSPOSE):   libname myxl xlsx "C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm";
ERROR: Invalid physical name for library MYXL.
ERROR: Error in the LIBNAME statement.
MPRINT(IMPORTANDTRANSPOSE):   set myxl.sheet1;
MPRINT(IMPORTANDTRANSPOSE):   range= "Sheet1$A:X";
MPRINT(IMPORTANDTRANSPOSE):   getnames= yes;
MPRINT(IMPORTANDTRANSPOSE):   run;


MPRINT(IMPORTANDTRANSPOSE):   proc sort data= sheet1;
ERROR: File WORK.SHEET1.DATA does not exist.
MPRINT(IMPORTANDTRANSPOSE):   by Type;
MPRINT(IMPORTANDTRANSPOSE):   run;

NOTE: The SAS System stopped processing this step because of errors.

I cross-check and see that the physical file is still there is this folder, I am wondering why they still announce that "Invalid physical name for library MYXL"

Best regards!

[Ksharp]

proc import datafile="c:\temp\ATC.xlsm" out=code_list replace;
run;
proc export data=code_list outfile="c:\temp\ATC.xlsx" dbms=xlsx replace;
run;

[Phil_NZ]

Hi @Ksharp !

Thank you very much for your solution, but this solution is doing well if the excel file (xlsm) has only one sheet. In my case, I ran the same code with an "xlsm" Excel file with 3 sheets and it turned out with a "xlsx" file with only the first sheet.

I am wondering if the result aligns with your idea.

[ballardw]

---

@Phil_NZ wrote:

Hi @Ksharp !

Thank you very much for your solution, but this solution is doing well if the excel file (xlsm) has only one sheet. In my case, I ran the same code with an "xlsm" Excel file with 3 sheets and it turned out with a "xlsx" file with only the first sheet.

I am wondering if the result aligns with your idea.

---

Export at heart pretty much wants to write a single "page" of output. It was designed with text files in mind. As such I'm not sure that you can get

[Tom]

Just tell EXPORT what SHEET to make. 

But don't use EXPORT or IMPORT. Just use the XLSX engine.

libname in xlsx "orignial.xlsm";
libname out xlsx "new.xlsx";
proc copy inlib=in outlib=out;
run;

[Phil_NZ]

Hi @Tom !

Thank you for your suggestion, I have tried and it still announce errors as below

My code is

OPTIONS MPRINT;
libname in xlsx "C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm";
libname out xlsx "C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsx";
proc copy inlib=in outlib=out;
run;

The log is:

28         OPTIONS MPRINT;
29         libname in xlsx "C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm";
ERROR: Invalid physical name for library IN.
ERROR: Error in the LIBNAME statement.
30         libname out xlsx "C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsx";
NOTE: Libref OUT was successfully assigned as follows: 
      Engine:        XLSX 
      Physical Name: C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsx


31         proc copy inlib=in outlib=out;
32         run;

ERROR: Libref IN is not assigned.

I am wondering if I haven't got your idea correctly?

Many thanks!