topic Re: PROC IMPORT Excel file .xlsb by using SAS 9.3 in SAS Programming

PROC IMPORT Excel file .xlsb by using SAS 9.3

weizhongma — Wed, 09 Apr 2014 08:38:54 GMT

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

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

RW9 — Wed, 09 Apr 2014 08:48:25 GMT

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.

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

weizhongma — Wed, 09 Apr 2014 12:57:36 GMT

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

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

RW9 — Wed, 09 Apr 2014 13:09:16 GMT

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.

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

weizhongma — Wed, 09 Apr 2014 14:15:07 GMT

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.

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

RW9 — Wed, 09 Apr 2014 14:23:24 GMT

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.

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

weizhongma — Wed, 09 Apr 2014 14:37:00 GMT

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!

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

Kurt_Bremser — Wed, 09 Apr 2014 14:37:48 GMT

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

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

RW9 — Wed, 09 Apr 2014 14:43:49 GMT

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.

Re: PROC IMPORT Excel file .xlsb by using SAS 9.3

RW9 — Wed, 09 Apr 2014 15:06:40 GMT

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