DATA Step, Macro, Functions and more

INFORMAT while importing XLSX in sas

Reply
Contributor
Posts: 38

INFORMAT while importing XLSX in sas

Hi all,

I am trying to import XLSX file in sas, But i have a FIle with attribute CISNO with values ~4.0161128E12(4016112842745)    which i have to convert into z15. format while imorting it

it is possible?

if it is can anyone tell me the code.

It will be very helpful Thanks

 

 

 

Respected Advisor
Posts: 3,777

Re: INFORMAT while importing XLSX in sas

You can associate that format after you have imported the data. Use PROC DATASETS; MODIFY
Contributor
Posts: 38

Re: INFORMAT while importing XLSX in sas

[ Edited ]

can you please tell me the example code if possible

Respected Advisor
Posts: 3,777

Re: INFORMAT while importing XLSX in sas

Look at the SAS documentation for PROC DATASETS.
Contributor
Posts: 38

Re: INFORMAT while importing XLSX in sas

there is any way I can use infile statement for XLSX file
like ex:
data imp;
infile "PATH\DDA_exclusion.csv" FIRSTOBS=2 dsd truncover ;
input CISNO: SVINTERNALACCNAME:$35. ;
format CISNO z15.;
run;
Frequent Contributor
Posts: 130

Re: INFORMAT while importing XLSX in sas

The only way to import .xlsx file into SAS is either using the import procedure, or if you have SAS Access you can use a libname statement.  You may be able to modify the format using a libname import, but you would have to look into that.  Here's a good paper on that technique:

 

http://www.lexjansen.com/pharmasug-cn/2014/PT/PharmaSUG-China-2014-PT09.pdf

Contributor
Posts: 38

Re: INFORMAT while importing XLSX in sas

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement
i have to change any settings ? in
FILE
-> IMPORT DATA
Frequent Contributor
Posts: 130

Re: INFORMAT while importing XLSX in sas

Do you have or have you installed SAS PC Files Server?  That's what I would first go and check.  If you haven't installed it, I would go here to figure out what you need and have to do:

 

http://support.sas.com/kb/52/649.html

 

You will need to use the pcfiles destination instead though.

Contributor
Posts: 38

Re: INFORMAT while importing XLSX in sas

Untitled.png

It is giving the above out put

so i think i have SAS/ACCESS Interface to PC Files

Frequent Contributor
Posts: 130

Re: INFORMAT while importing XLSX in sas

[ Edited ]

Then it depends where the xlsx file is located at.  If it's on Linux or UNIX, then you shouldn't need to use the PCFILES libname engine.  If it's stored on Windows, then you will need to use it.

 

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n0oj9f6i838mymn148...

 

Also, I see you have the SAS/ACCESS with ODBC.  In that case, you can also create an ODBC connection to the excel file and import it that way.

Contributor
Posts: 38

Re: INFORMAT while importing XLSX in sas

Actually it is on windows. i dont know what i am doing wrong , actually it so simple statement
libname workbk2 'C:\Users\speesary\Desktop\programs\sas programs\ddaoutput\oct_dda\DDA_exclusion.xlsx' ;
Frequent Contributor
Posts: 130

Re: INFORMAT while importing XLSX in sas

Did you read the link I provided?  You need to use the PCFILES engine in the libname statement if it's on Windows.

 

LIBNAME myxlsx PCFILES SERVER=D2323 PATH='c:\demo.xlsx';

DATA a;
   SET myxlsx.'sheet1$'n;
RUN;

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n1qk7lv0f535qkn1my...

Contributor
Posts: 38

Re: INFORMAT while importing XLSX in sas

libname mydblib odbc datasrc=ALMDATA user=Siddharth password=ALM63Floor schema = dbo;
actually i use the above code to pull data from mssql
so we ca also use the ODBC code to puul from XLSX file then?
Super User
Super User
Posts: 6,502

Re: INFORMAT while importing XLSX in sas

You cannot use INFILE with an XLSX file. Well you could, but since it is a ZIP compressed archive of a series of XML documents it will be very hard to read with a data step.

 

You can use a LIBNAME with the XLSX engine to point to the file. 

 

libname mylib XLSX 'DDA_exclusion.xlsx' ;
proc copy inlib=mylib outlib=work; run;

But there is no way to tell SAS how to interpret the XLSX file columns.

 

If the values come across as numbers then you should be able to attach the Z15. format to the variable as that the values are displayed with leading zeros.

 

 

Contributor
Posts: 38

Re: INFORMAT while importing XLSX in sas

Untitled.png

These the above error i am getting when i use it  @Tom

Ask a Question
Discussion stats
  • 15 replies
  • 708 views
  • 0 likes
  • 4 in conversation