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

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

yaswanthj — Wed, 26 Jul 2017 17:23:22 GMT

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.

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

allurai0412 — Fri, 17 May 2013 11:34:03 GMT

hi,

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

Regards

Allu

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

esjackso — Fri, 17 May 2013 11:52:57 GMT

Are you getting an error ...?

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

yaswanthj — Fri, 17 May 2013 12:06:56 GMT

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

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

esjackso — Fri, 17 May 2013 12:23:02 GMT

Are you running sas on a server or local machine?

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

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

yaswanthj — Fri, 17 May 2013 12:51:39 GMT

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

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

allurai0412 — Fri, 17 May 2013 13:47:34 GMT

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

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

yaswanthj — Fri, 17 May 2013 14:03:26 GMT

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.

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

esjackso — Fri, 17 May 2013 14:40:12 GMT

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.

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

Tom — Fri, 17 May 2013 14:40:21 GMT

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.

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

KevinViel — Wed, 26 Jul 2017 17:34:29 GMT

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.

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

Cynthia_sas — Fri, 20 Jun 2014 23:00:54 GMT

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

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

jakarman — Sat, 21 Jun 2014 04:23:03 GMT

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.

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

KevinViel — Sat, 21 Jun 2014 14:28:42 GMT

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

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

Cynthia_sas — Sat, 21 Jun 2014 18:56:58 GMT

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

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

slchen — Sat, 21 Jun 2014 20:54:28 GMT

Check this link: Reading XLSX Excel files with SAS/BASE macro - the easy way - sasCommunity

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

gboor — Mon, 24 Aug 2015 09:46:56 GMT

Maybe it is due to the typo?

I see a .xlxs extension, which should be a .xlsx extension...

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

msteelman — Tue, 06 Dec 2016 22:25:57 GMT

I use this for code for xls and xlsx files

&prjdir is a macro for the file path.

&file1 and &sheet1 are macros for the file name and sheet name you want to read

filename case DDE "Excel|&prjdir.[&file1.]&sheet2.!R<first row>C<first column>:R<last row>C<last column>" notab;
data want;
infile case dsd dlm='09'x missover pad lrecl=1500;
length <vars with lengths>;
input <vars>;
run;
filename case clear;

So a filled in example.

%LET prjdir=C:\Desktop\WR213548\;

%LET file1=test;

%LET sheet1=sheet1;

filename case DDE "Excel|&prjdir.[&file1.]&sheet2.!R1C1:R1000C25" notab; *1000 rows 25 columns;
data want;
infile case dsd dlm='09'x missover pad lrecl=1500; *change lrecl to fit your full row length;
length var1-var25 $20.;
input var1-var25;
run;
filename case clear;

Edit as needed. Make sure that you have your excel file open and enabled editing before running this or you'll get an error.