BookmarkSubscribeRSS Feed
weizhongma
Calcite | Level 5

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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 Smiley Wink

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 14311 views
  • 0 likes
  • 3 in conversation