BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yaswanthj
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
KevinViel
Pyrite | Level 9

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

17 REPLIES 17
allurai0412
Fluorite | Level 6

hi,

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

Regards

Allu

esjackso
Quartz | Level 8

Are you getting an error ...?

yaswanthj
Calcite | Level 5

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

esjackso
Quartz | Level 8

Are you running sas on a server or local machine?

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

EJ

yaswanthj
Calcite | Level 5

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

allurai0412
Fluorite | Level 6

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

yaswanthj
Calcite | Level 5

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.

esjackso
Quartz | Level 8

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

Tom
Super User Tom
Super User

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.

KevinViel
Pyrite | Level 9

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

Cynthia_sas
SAS Super FREQ

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

jakarman
Barite | Level 11

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 --<-----
KevinViel
Pyrite | Level 9

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

Cynthia_sas
SAS Super FREQ


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

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
  • 17 replies
  • 90851 views
  • 4 likes
  • 10 in conversation