INFORMAT while importing XLSX in sas

[siddharthpeesary]

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

[data_null__]

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

[siddharthpeesary]

can you please tell me the example code if possible

[data_null__]

Look at the SAS documentation for PROC DATASETS.

[siddharthpeesary]

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;

[dcruik]

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

[siddharthpeesary]

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

[dcruik]

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.

[siddharthpeesary]

It is giving the above out put

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

[dcruik]

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.

[siddharthpeesary]

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

[dcruik]

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...

[siddharthpeesary]

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?

[Tom]

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.

[siddharthpeesary]

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