topic Re: INFORMAT while importing XLSX in sas in SAS Programming

INFORMAT while importing XLSX in sas

siddharthpeesary — Tue, 15 Dec 2015 18:01:19 GMT

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

Re: INFORMAT while importing XLSX in sas

data_null__ — Tue, 15 Dec 2015 18:15:27 GMT

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

Re: INFORMAT while importing XLSX in sas

siddharthpeesary — Tue, 15 Dec 2015 18:17:06 GMT

can you please tell me the example code if possible

Re: INFORMAT while importing XLSX in sas

data_null__ — Tue, 15 Dec 2015 18:18:55 GMT

Look at the SAS documentation for PROC DATASETS.

Re: INFORMAT while importing XLSX in sas

siddharthpeesary — Tue, 15 Dec 2015 18:23:16 GMT

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;

Re: INFORMAT while importing XLSX in sas

dcruik — Tue, 15 Dec 2015 19:33:00 GMT

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

Re: INFORMAT while importing XLSX in sas

siddharthpeesary — Tue, 15 Dec 2015 19:35:00 GMT

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

Re: INFORMAT while importing XLSX in sas

dcruik — Tue, 15 Dec 2015 19:39:11 GMT

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.

Re: INFORMAT while importing XLSX in sas

siddharthpeesary — Tue, 15 Dec 2015 19:43:54 GMT

It is giving the above out put

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

Re: INFORMAT while importing XLSX in sas

dcruik — Tue, 15 Dec 2015 19:50:21 GMT

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#n0oj9f6i838mymn148890ckla700.htm

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.

Re: INFORMAT while importing XLSX in sas

siddharthpeesary — Tue, 15 Dec 2015 19:51:18 GMT

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' ;

Re: INFORMAT while importing XLSX in sas

siddharthpeesary — Tue, 15 Dec 2015 19:54:33 GMT

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?

Re: INFORMAT while importing XLSX in sas

Tom — Tue, 15 Dec 2015 19:54:34 GMT

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.

Re: INFORMAT while importing XLSX in sas

dcruik — Tue, 15 Dec 2015 19:55:00 GMT

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#n1qk7lv0f535qkn1mydookwetyrp.htm

Re: INFORMAT while importing XLSX in sas

siddharthpeesary — Tue, 15 Dec 2015 19:57:41 GMT

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

Re: INFORMAT while importing XLSX in sas

Tom — Tue, 15 Dec 2015 20:14:09 GMT

You probably need to use a more current version of SAS for the XLSX engine to work.