topic Re: import excel XLS in SAS Studio

import excel XLS

navinrraj — Fri, 10 Aug 2018 03:35:55 GMT

i try to run this file

this error was show up here *database missing

"proc import datafile="/home/naveence0/food2.xlsx"
out= indata
dbms=xlsx replace;
run;
proc print data = indata; ,,

please if tat code having nay changes please descriped that.

one main questions wat is file extension of xls file

Re: import excel XLS

Kurt_Bremser — Fri, 10 Aug 2018 06:14:11 GMT

When you need help with an ERROR, always post the whole log of the failing step.

Copy/paste the log into a window opened with the {i} button.

Re: import excel XLS

RW9 — Fri, 10 Aug 2018 07:56:54 GMT

Why is the proc import statement in quotes?

"proc import datafile="/home/naveence0/food2.xlsx"

That is invalid. Run:

proc import datafile="/home/naveence0/food2.xlsx" out= indata dbms=xlsx replace;
run;

Re: import excel XLS

navinrraj — Fri, 10 Aug 2018 13:10:46 GMT

proc import datafile="/home/naveence0/food2.xlsx"
out= indata
dbms=xlsx replace;
run;
proc print data = indata;

Re: import excel XLS

RW9 — Fri, 10 Aug 2018 13:16:56 GMT

Yes, that is what I posted. Post your log, so we can see the error message and the code you run. Did you put the file in your shared directory?

https://communities.sas.com/t5/SAS-Analytics-U/Importing-Excel-file-to-SAS-Studio/m-p/216686#M2443

Re: import excel XLS

navinrraj — Fri, 10 Aug 2018 13:29:08 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

71 proc import datafile='/home/naveence0/food3.csv'

72 out = datainput

73 dbms = excel

74 ;

ERROR: DBMS type EXCEL not valid for import.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

whic extension should i use excel sheet to sas on windows

system cpu time 0.00 seconds

memory 44.00k

OS Memory 26532.00k

Timestamp 08/10/2018 01:25:47 PM

Step Count 38 Switch Count 0

Page Faults 0

Page Reclaims 22

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

75 run;

Re: import excel XLS

RW9 — Fri, 10 Aug 2018 13:37:52 GMT

Well, you will see in the log you have given there, you have a CSV file -

'/home/naveence0/food3.csv'

And your telling SAS that its supposed to read that file using the Excel engine, hence you get an invalid DBMS error.

If the file is CSV, then your dbms should be csv. However I would recommend taking the code generated from proc import (when you change dbms=csv) and then using that to create a datastep import, fixing the formats and such like. When you run proc import, it will put the code it generates in the log.

Re: import excel XLS

navinrraj — Fri, 10 Aug 2018 13:45:22 GMT

So please tell me .
What the extension of excel file
How convert to sas
And dbms value

Re: import excel XLS

RW9 — Fri, 10 Aug 2018 13:54:22 GMT

"What the extension of excel file" - File extensions tell the operating system and other tools what the file is. It doesn't necessarily need to match the file type, however it really isn't very good if it doesn't. Excel has a few different file extensions, XLS is the old binary file format, XLSX is the new Open Office file format. CSV is not an Excel file format, it is plain text with data delimited by comma's, Excel can however read an parse this file type.

"How convert to sas" - this depends on several factors. What exactly is the file type you are trying to import, is it a CSV file?

"And dbms value" - DBMS value indicates to SAS what type of file you are trying to import. Again, I do not know what you have, is it a CSV file, is it an XLSX file?

If its a csv file then:

proc import datafile="/home/naveence0/food2.csv" out=indata dbms=csv replace;
run;