04-09-2014 04:38 AM
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.
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"
04-09-2014 04:48 AM
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.
04-09-2014 09:09 AM
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;
See what that does.
04-09-2014 10:15 AM
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.
04-09-2014 10:23 AM
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.
04-09-2014 10:37 AM
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!
04-09-2014 10:43 AM
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.
04-09-2014 11:06 AM
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.).
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\" '
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
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
xRow = xRow + 1
xFname$ = Dir
04-09-2014 10:37 AM
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