DATA Step, Macro, Functions and more

How To import .xlsx file in to sas using infile statment

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

How To import .xlsx file in to sas using infile statment

[ Edited ]

Hi All,

 

Can any one help how can i import ".xlsx" file into SAS using "infile" Statement

 

i have trying with below code. but i can not able to import the file..

 

infile 'C:\Desktop\WR213548\test.xlxs' MISSOVER DSD lrecl=32767 firstobs=2 ;

 

how can i get the .xlsx using "infile statement" and im not able to use the proc import since i don`t have EXCEL installed in PC( or Server)

 

Thanks in advance..

 

Regards,

Yaswanth J.


Accepted Solutions
Solution
3 weeks ago
Frequent Contributor
Posts: 79

Re: How To import .xlsx file in to sas using infile statment

[ Edited ]

Editor's note: we modified this popular topic reply to be more relevant for current readers.

 

You cannot INFILE an XLSX file in DATA step.  You need to use an engine that can read the data structure of an Excel spreadsheet.

 

If you have SAS/ACCESS to PC Files, you can use PROC IMPORT DBMS=XLSX or LIBNAME XLSX to accomplish this.  This works on machines even where Excel is not installed (such as Linux).  Both of these methods work in SAS University Edition if you're using that.

 

If you don't have SAS/ACCESS to PC Files licensed/installed, and you do have Excel installed on a local Windows machine along with SAS, you can try to use DDE.  That's a method of using SAS to "talk to" Excel to exchange information about your data.  Note that DDE typically won't work when SAS is running on a remote server.

 

More on DDE:

 

1) Read Koen Vyverman's excellent DDE papers (here's one).

2) Excel is not a text file, so INFILE'ing it as ASCII text won't yield much:

 

  %let ws = Sheet1 ;
  %let r1 = 7   ;
  %let c1 = 2   ;
  %let r2 = 534 ;
  %let c2 = 23  ;

 

  FileName XL
           DDE
           "Excel|&ws!r&r1.c&c1.:r&r2.c&c2."
           LRecL = 5000
           NoTab
           ;

 

  Data specs ;
    Infile XL
           DSD
           Pad
           DLM = "09"x
           ;
    Length Col1 - Col%eval( &c2. - &c1. - 1 ) $ 200 ;
    Input Col1 - Col%eval( &c2. - &c1. - 1 ) ;
  Run ;


HTH,

 

Kevin

View solution in original post


All Replies
Frequent Contributor
Posts: 97

Re: How To import .xlsx file in to sas using infile statment

hi,

Please convert Excel to Csv ...and try...

Regards

Allu

Super Contributor
Posts: 333

Re: How To import .xlsx file in to sas using infile statment

Are you getting an error ...?

Contributor
Posts: 70

Re: How To import .xlsx file in to sas using infile statment

Getting below error...

ERROR: Physical file does not exist,

       D:\Temp\yaswanth\Astellas_Codelists\WR213548\MedDRA_15.0_Map.xlsx.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set LIB.MEDDRA160_MAPPINGS may be incomplete.  When this step was stopped there

         were 0 observations and 12 variables.

NOTE: DATA statement used (Total process time):

      real time           0.06 seconds

      cpu time            0.04 seconds

Super Contributor
Posts: 333

Re: How To import .xlsx file in to sas using infile statment

Are you running sas on a server or local machine?

Does the install have access to the path of the file?

EJ

Contributor
Posts: 70

Re: How To import .xlsx file in to sas using infile statment

Im Running the job  in local machine and has access to the path of the file..every thing got set up.


can not able to access the the .xlsx file using infile statment

Frequent Contributor
Posts: 97

Re: How To import .xlsx file in to sas using infile statment

hi ,

I have some queries  in your question..

1) you have stated ..there is no EXCEL in pc....how can we distnguish that the file type on your Drive is Excel ??/

2)please go to file source and right click on properties....this will show the DBMS type (txt or xls or db or pdf)

3)And the statment   'lrecl=32767' ....this means your are able open the file !!!! and declaring the MAXIMUM Row  (record) length=32767???

Regards

Allu

Contributor
Posts: 70

Re: How To import .xlsx file in to sas using infile statment

Hi Allu,

1. The client specified and provided me the EXcel(.xlsx) file..

2. Its saying unknown application.

3. Previously my client provided CSV, so i used 'lrecl=32767' over there. i did not changed that.

Super Contributor
Posts: 333

Re: How To import .xlsx file in to sas using infile statment

What SAS components are installed and what version?

You can run a proc setinit; run; to find this out.

I dont know this for sure but you might have to have SAS/Access for PC files even for the infile statement. If you dont have this then your only option (I think) is to convert to a txt file either through excel or open office or google docs.

I would copy the path directly from the explorer to make sure there wasnt a small typo somewhere as well. On multiple occasions I have been hit by the realization that I had just typed the path wrong.

EJ

Super User
Super User
Posts: 6,499

Re: How To import .xlsx file in to sas using infile statment

If you do not have Excel then you should ask the client to resave the data as CSV file and resend.

You might try using Google documents or some other tool to convert the file yourself outside of SAS.

Solution
3 weeks ago
Frequent Contributor
Posts: 79

Re: How To import .xlsx file in to sas using infile statment

[ Edited ]

Editor's note: we modified this popular topic reply to be more relevant for current readers.

 

You cannot INFILE an XLSX file in DATA step.  You need to use an engine that can read the data structure of an Excel spreadsheet.

 

If you have SAS/ACCESS to PC Files, you can use PROC IMPORT DBMS=XLSX or LIBNAME XLSX to accomplish this.  This works on machines even where Excel is not installed (such as Linux).  Both of these methods work in SAS University Edition if you're using that.

 

If you don't have SAS/ACCESS to PC Files licensed/installed, and you do have Excel installed on a local Windows machine along with SAS, you can try to use DDE.  That's a method of using SAS to "talk to" Excel to exchange information about your data.  Note that DDE typically won't work when SAS is running on a remote server.

 

More on DDE:

 

1) Read Koen Vyverman's excellent DDE papers (here's one).

2) Excel is not a text file, so INFILE'ing it as ASCII text won't yield much:

 

  %let ws = Sheet1 ;
  %let r1 = 7   ;
  %let c1 = 2   ;
  %let r2 = 534 ;
  %let c2 = 23  ;

 

  FileName XL
           DDE
           "Excel|&ws!r&r1.c&c1.:r&r2.c&c2."
           LRecL = 5000
           NoTab
           ;

 

  Data specs ;
    Infile XL
           DSD
           Pad
           DLM = "09"x
           ;
    Length Col1 - Col%eval( &c2. - &c1. - 1 ) $ 200 ;
    Input Col1 - Col%eval( &c2. - &c1. - 1 ) ;
  Run ;


HTH,

 

Kevin

SAS Super FREQ
Posts: 8,743

Re: How To import .xlsx file in to sas using infile statment

Hi, One challenge with XLSX files (like DOCX and PPTX files) is that it is a NEW Microsoft format. In fact, an .XLSX file is not 1 file, but is a zip archive of multiple related files. You can prove this to yourself by taking any simple XLSX file, copying it and then renaming the file extension of the copy to .ZIP, Windows will complain about doing this, but you can rename the file. Then, open the .zip file with WinZip or an unzipping program. You will see that the XLSX file is not just 1 file, but is a collection of related, mostly XML files.

cynthia

Valued Guide
Posts: 3,208

Re: How To import .xlsx file in to sas using infile statment

Yes, Cyntia it is a zip with XML and the way you can proof that is reading using a zip-top.

The ms way are add-ons to those  https://www.oasis-open.org/committees/tc_home.php?wg_abbrev=office as ms did the most work. New? Not really the mentioned year is 2005 and with MS it came with 2007.


The coloring and more are field attributes within those XML's.

It is strange SAS missed this all.

---->-- ja karman --<-----
Frequent Contributor
Posts: 79

Re: How To import .xlsx file in to sas using infile statment

Cynthia,

  Thank you for the clarification.  How does this affect using DDE within SAS?  If I understand correctly, the SAS System acts a client "requesting" the "server" Excel to perform the task, for instance to insert a value into a cell or format the cell (format, border, font, shading, et cetera).  Naively, I would expect Excel to handle the various files correctly.  Unless I misunderstand, this is not editing the (zipped XML) files directly, but rather opening Excel and have it perform the actions.

  One thing is for sure, I have to become more familiar with XML.  I appreciate the clarification from you and Jaap.

Kind regards,

Kevin

SAS Super FREQ
Posts: 8,743

Re: How To import .xlsx file in to sas using infile statment


Hi:

  I'm not much help. I avoid DDE as much as possible because usually my SAS is on a server that doesn't have Office and therefore, the whole DDE process is un-doable. And, DDE is older Microsoft technology. I just don't use it. And, although I can't use ODS to insert 1 value in 1 cell in an existing sheet, I can use ODS to impact the cell format, border, font shading, et cetera) of report output, so usually, I manage to get what I want with ODS.

cynthia

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 24679 views
  • 4 likes
  • 10 in conversation