As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Reading XLSX Excel files with SAS/BASE macro - the easy way

From sasCommunity
Jump to: navigation, search

Many SAS customers are struggling to import XLSX files into SAS data sets. SAS supports many confusing ways to do it:

1. You must have a license to SAS/Acess to PC file formats (Which not all customers owns $$$ especially not on Unix/Linux sites).

2. XLSX is not supported directly prior to Ver. 9.3 (and there are many sites still in 9.1 9.2)

3. SAS 9.3 does not support NLS encoding in DBMS=XLSX engine.

4. There are many engines to select from which gives different results.

5. You may need a separate "PC-FILE-SERVER" to place your files on.

6. Other ways like DDE CSV are not reliable or applicable for batch processing.


Taking this challenge we have developed a unique SAS/BASE only macro: %xlsx2sas


Example:

%xlsx2sas(

          infilename    = d:\demo.xlsx  ,
          outdata       = work.demo     ,
          sheetnumber   = 1             ,
          xlsxrows      = ALL           ,
          xlsxcols      = ALL           ,
          xlsxlabels    = no            ,
          xlsxmix       = ALL           ,
          xlsxdebug  = YES              ,
          xlsxdropvars=NO               ,
          xlsxdropobs=YES               ,
          xlsxmaxlen=1000                
        );

The macro uses only SAS/BASE code and hence can work on 9.1 and above on any platform.

The macro reads the XLSX file directly.


Supports: SAS 9.1, 9.2, 9.3, 9.4 (Windows/Linux/Unix and even MVS*) platforms (32/64 bit)

NO DDE, NO CSV, NO SAS/Access - just plain SAS/BASE code - No limits.


Download free trial at: www.bixforsas.com


You are all invited to test %xlsx2sas on your Excel spreadsheets.


  • MVS users: please email support@bixforsas.com to check availability.