PROC IMPORT Excel file .xlsb by using SAS 9.3

[weizhongma]

Hi there,

I am trying to import an excel file (extension xlsb) into SAS. One of the fields contain a mix between Numeric and Character, e.g.

Record	Contract Id
1	8522340951
2	8522213022
3	8522212766
4	8522366129
5
6	MM1309200020
7	MM1309500016
8	MM1309900001
9	MM1309900012

I use the following codes which default the field to be numeric, thus, record 6 to 9 becomes blank in the dataset. How do I bring in the field to be character?

PROC IMPORT OUT=cdepositbuffer

            DATAFILE= "C:\Users\1144082\Documents\wfh\work\scb\20140409 - sas93 migration\input\Deposit Buffer 20140331.xlsb"

            DBMS=EXCELCS REPLACE;

            sheet="sheet1";

RUN;

Regards,

Weizhong

[RW9]

Hi,

Try adding mixed=yes; after sheet line.  However you may find that your system is setup to only check a few records, so you may need to update guessingrows in your registry to a higher numer (i.e. number of rows it checks to see type of format).  Alternatively save the xlsb as csv and read it in manually using infile where you can setup explicit formats/lengths etc.

[weizhongma]

Thanks! But SAS 9.3 does not accept MIXED = YES for .xlsb format.

[RW9]

Oh, it works on xlsx, how annoying.  Have you tried libnaming to the file:

libname my_xls excel "S:\Temp\Rob\tmp.xlsx" mixed=yes;

then:

data test;

     set my_xls.'sheet1$'n;

run;

See what that does.

[weizhongma]

Is there a way for MIXED = YES working for .xlsb format in SAS 9.3? I have many files in .xlsb format and it is not possible to change all of them to .xlsx format.   

[RW9]

Did you try the libname approach?

Two other things I can think of unless someone has the mixed working with.xlsb files:

-> Have a master Excel file with a VBA macro in.  That macro could open each file, say in a selected folder, and save it out as .xlsx.  Then you would be able to use the above examples.

-> Read the data directly from the underlying xml.  This is a difficult path however, unzip the file, then parse the xml in the subfolders.

[weizhongma]

Hi, tried. It works. Thanks a lot!

However I do not want to change my original Excel files. Does MIXED=YES not work for .xlsb format? Anyone can confirm this? Thanks! 

[RW9]

Yep, would agree with KurtBremser there, avoid Excel for any vital data transfer.  However you don't need to modify the original files.  In my example of the Excel Macro what I would do is to setup a folder called Data\In, and a folder Data\Out.  Put your original files in the in area. then write a master Excel file with one macro which opens each file in \In, then calls the save as Excel function to save the file to the \Out folder.  Hence you would have two versions, the original and the new one.  Then use the new ones to read into SAS. 

[RW9]

Having utilized a macro found online and manipulating it to do much what you want, here is a VBA macro.  So have a data\In nad data\out somewhere.  Put your xlsb files in the in.  then create a master excel file and and the macro into the code (alt-f11).  Then alt-f8 run the code.  It will ask you to locate the in folder, then open each file in that folder, saving the file as xlsx, then closing that file.  You don't actually even need to save the master.  Note this was just a quick manipulation to show operation not a full blown set of code (which should have error checking etc.).

Sub GetFileNames()

    Dim xRow As Long

    Dim xDirect$, xFname$, InitialFoldr$

    InitialFoldr$ = "C:\" '

    With Application.FileDialog(msoFileDialogFolderPicker)

        .InitialFileName = Application.DefaultFilePath & "\"

        .Title = "Please select a folder to list Files from"

        .InitialFileName = InitialFoldr$

        .Show

        If .SelectedItems.Count <> 0 Then

            xDirect$ = .SelectedItems(1) & "\"

            xFname$ = Dir(xDirect$, 7)

            Do While xFname$ <> ""

            Set wb = Workbooks.Open("s:\temp\rob\data\in\" & xFname$)

            wb.SaveAs "s:\temp\rob\data\out\" & Replace(xFname$, ".xlsb", ".xlsx"), FileFormat:=51

            wb.Close False

            xRow = xRow + 1

            xFname$ = Dir

        Loop

        End If

    End With

End Sub

[Kurt_Bremser]

xlsb is a _very_ poprietary format that is not officially documented, therefore it is hard/impossible/illegal to parse it outside of MS Office. It is also prone to complete loss of data if something happens to the file (Compressed xml can usually be recoverd up to the error point).

After my experiences with xls vs. ods, doc vs. odt or xls vs. xlsx I will stay away from xlsb.

OTOH, I try to avoid Excel anyway. I've got LibreOffice for small things and SAS for serious work