BookmarkSubscribeRSS Feed
siddharthpeesary
Calcite | Level 5

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

 

 

 

15 REPLIES 15
data_null__
Jade | Level 19
You can associate that format after you have imported the data. Use PROC DATASETS; MODIFY
siddharthpeesary
Calcite | Level 5

can you please tell me the example code if possible

data_null__
Jade | Level 19
Look at the SAS documentation for PROC DATASETS.
siddharthpeesary
Calcite | Level 5
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
Lapis Lazuli | Level 10

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
Calcite | Level 5
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement
i have to change any settings ? in
FILE
-> IMPORT DATA
dcruik
Lapis Lazuli | Level 10

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
Calcite | Level 5

Untitled.png

It is giving the above out put

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

dcruik
Lapis Lazuli | Level 10

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
Calcite | Level 5
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
Lapis Lazuli | Level 10

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
Calcite | Level 5
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
Super User Tom
Super User

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
Calcite | Level 5

Untitled.png

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

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
  • 15 replies
  • 2639 views
  • 0 likes
  • 4 in conversation