topic Re: Is there anyway to import a file xlsm to xlsx in SAS ? in SAS Programming

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

Phil_NZ — Wed, 06 Jan 2021 19:48:19 GMT

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!

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

SASKiwi — Wed, 06 Jan 2021 20:00:09 GMT

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.

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

Tom — Wed, 06 Jan 2021 20:59:11 GMT

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;

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

Phil_NZ — Thu, 07 Jan 2021 01:04:59 GMT

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.

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

Shmuel — Thu, 07 Jan 2021 01:49:48 GMT

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.

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

Phil_NZ — Thu, 07 Jan 2021 02:13:08 GMT

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

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

ballardw — Thu, 07 Jan 2021 02:30:42 GMT

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.

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

Tom — Thu, 07 Jan 2021 03:34:56 GMT

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

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

Shmuel — Thu, 07 Jan 2021 07:01:58 GMT

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

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

Ksharp — Thu, 07 Jan 2021 12:59:29 GMT

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;

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

Phil_NZ — Fri, 08 Jan 2021 02:22:14 GMT

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.

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

Phil_NZ — Fri, 08 Jan 2021 02:30:32 GMT

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!

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

ballardw — Fri, 08 Jan 2021 02:58:40 GMT

@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

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

Tom — Fri, 08 Jan 2021 04:19:28 GMT

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;

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

Phil_NZ — Fri, 08 Jan 2021 04:24:28 GMT

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!

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

Tom — Fri, 08 Jan 2021 04:42:26 GMT

I got a different error. Perhaps PROC COPY is trying to do something strange?

Anyway it worked when it first copied to actual SAS datasets.

libname in xlsx 'c:\downloads\test1.xlsm';
libname out xlsx 'c:\downloads\test1.xlsx';

proc datasets nolist kill lib=work mt=data; run;

proc copy inlib=in outlib=work; run;
proc copy inlib=work outlib=out mt=data; run;

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

Phil_NZ — Fri, 08 Jan 2021 04:47:03 GMT

So, in general:

When I run the code:

proc import datafile="C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm" out=code_list replace;
run;
proc export data=code_list outfile="C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsx" dbms=xlsx replace;
run;

It will generate a new xlsx file in the folder named (New Folder) with only sheet1 (among 5 sheets in total)

When I run the code with libname:

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;

It turned out the error as below:

28         libname in xlsx 'C:\Users\pnguyen\Desktop\New folder\Argentina__.xlsm';
ERROR: Invalid physical name for library IN.
ERROR: Error in the LIBNAME statement.
29         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


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

ERROR: Libref IN is not assigned.

And I am not sure I can you call sheet 1, or sheet2 from the code of libname approach, can you please give me a hint, I am really into this way of doing thing?

Many thanks and best regards.

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

Phil_NZ — Fri, 08 Jan 2021 04:50:52 GMT

Thanks @Tom

It is quite interesting to me, even I tried the new code suggested, it still turned out the same error, so it maybe because of the PROC COPY, I deem

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 datasets nolist kill lib=work mt=data; run;

proc copy inlib=in outlib=work; run;
proc copy inlib=work outlib=out mt=data; run;

Log:

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

Many thanks!

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

Tom — Fri, 08 Jan 2021 05:18:43 GMT

The error message is very specific and strange. Try copying or renaming the file.

Also what version of SAS are you running?

124   %put &sysvlong;
9.04.01M5P091317

What version of Excel made the file?

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

Phil_NZ — Fri, 08 Jan 2021 05:25:18 GMT

Hi @Tom

I am using SAS EG 8.2 (32 bit)

My Excel version is:

I also attach the file (xlsx) and you can convert to xlsm by saving as and test in case

Many thanks and regards!